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!
