Blog Post

Azure Database Support Blog
1 MIN READ

Lesson Learned #26: How to change the definition of table triggers at the same time

Jose_Manuel_Jurado's avatar
Jose_Manuel_Jurado
Icon for Microsoft rankMicrosoft
Mar 14, 2019
First published on MSDN on Apr 10, 2017
Today, we have been working a very interesting case, when our customer is migrating their database to Azure SQL Database.

We have found an incompatibility issue, because all triggers have an error handler with this syntax: raiserror @errno @errmsg to inform the error happened, but, this syntax is not supported for Azure SQL Database.

To fix this problem, we need to replace the syntax raiserror @errno @errmsg by, for example, raiserror (@errno,-1,-1, @errmsg). Unfortunately, we have around 100 triggers to modify accordingly.

In this situation, we suggested the following script "as is - without any guarantee" in order to obtain the definition of all triggers to review it.


DECLARE @TRname nvarchar(MAX)


DECLARE @N as int


DECLARE vcursor CURSOR FOR select OBJECT_DEFINITION(OBJECT_ID(NAME)) AS xTriggerDefinitionx from sys.objects where type ='TR';


OPEN vcursor


FETCH NEXT FROM vCursor INTO @TRNAME


WHILE @@FETCH_STATUS = 0


BEGIN


--PRINT @TRNAME


SET @N = charindex('raiserror @errno @errmsg',@TRname)


SET @Trname = REPLACE(@TRname,'CREATE TRIGGER','ALTER TRIGGER')


If @n<> 0


BEGIN


SET @Trname = REPLACE(@TRname,'raiserror @errno @errmsg','raiserror(@errno,-1,-1, @errmsg)')


END


PRINT @TRNAME


FETCH NEXT FROM vCursor INTO @TRNAME


END


CLOSE vcursor;


DEALLOCATE vcursor;




Enjoy!
Updated Mar 14, 2019
Version 2.0
azure sql database
object_definition
raiserror
trigger
  • jmbruges's avatar
    jmbruges
    Copper Contributor
    Mar 07, 2022

    Thank you José Jose_Manuel_Jurado .

     

    I can say first that your scripts worked as a charm. I could get easily a list of depracated ernno instructions and manually change the tables (excecute command). I could have make an automatic script but since there was just 140 tables I thought it is better to do it manually this time. The migration issue was solve so the DB is up to 2019 Microsoft SQL version.

     

    I never used before SQL Management studio but it was not that difficult to figure it out.

     

    If something similar happen when chosing Azure tool then I will defenetly create a post.

     

    So far I can just tell to anyone looking for similar problem, use Jose's code.

     

    /Javier

  • Jose_Manuel_Jurado's avatar
    Jose_Manuel_Jurado
    Icon for Microsoft rankMicrosoft
    Mar 05, 2022

    Thanks jmbruges and my apologies for my delay. 

     

    I would like to suggest to create a new service request from the Azure Portal that any of my colleagues could work with you in this migration issue. You could add my name as a reference.

  • jmbruges's avatar
    jmbruges
    Copper Contributor
    Feb 28, 2022

    Hi Jose Manuel,

     

    Jose_Manuel_Jurado Long time since you wrote this post, but I was wondering if you could provide some help.

     

    I have the same problem (migrating a really old DB from a customer: using SQL 2005). So the first problem is syntax compatibility.

     

    I tried your code as a new query and it works perfectly, but I really do'nt know how to implement the migration. Let me explain: I run the query, I get all the changes in the "Message tab", but that does not have the same effect in the real DB. When I check the tables in the Runtime they still are the same.

     

    My main question is, how can I run your code to obtain an updated version of the DB?

     

    I am very new in DB and SQL, that reflects the level of my comment.

     

    Thanks in advance.

    Javier