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

  • Article

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!