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

Published Mar 13 2019 06:40 PM 132 Views
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!
%3CLINGO-SUB%20id%3D%22lingo-sub-368879%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2326%3A%20How%20to%20change%20the%20definition%20of%20table%20triggers%20at%20the%20same%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-368879%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Apr%2010%2C%202017%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20Today%2C%20we%20have%20been%20working%20a%20very%20interesting%20case%2C%20when%20our%20customer%20is%20migrating%20their%20database%20to%20Azure%20SQL%20Database.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20We%20have%20found%20an%20incompatibility%20issue%2C%20because%20all%20triggers%20have%20an%20error%20handler%20with%20this%20syntax%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Flanguage-elements%2Fraiserror-transact-sql%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20raiserror%20%3C%2FA%3E%20%40errno%20%40errmsg%20to%20inform%20the%20error%20happened%2C%20but%2C%20this%20syntax%26nbsp%3Bis%20not%20supported%20for%26nbsp%3BAzure%20SQL%20Database.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20To%20fix%20this%20problem%2C%20we%20need%20to%20replace%20the%20syntax%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Flanguage-elements%2Fraiserror-transact-sql%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20raiserror%20%3C%2FA%3E%20%40errno%20%40errmsg%20by%2C%20for%20example%2C%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Flanguage-elements%2Fraiserror-transact-sql%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20raiserror%20%3C%2FA%3E%20(%40errno%2C-1%2C-1%2C%20%40errmsg).%20Unfortunately%2C%20we%20have%20around%20100%20triggers%20to%20modify%20accordingly.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20In%20this%20situation%2C%20we%20suggested%20the%20following%20script%26nbsp%3B%22as%20is%20-%20without%20any%20guarantee%22%20in%20order%20to%20obtain%20the%20definition%20of%20all%20triggers%20to%20review%20it.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3EDECLARE%20%40TRname%20nvarchar(MAX)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40N%20as%20int%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20vcursor%20CURSOR%20FOR%20select%20OBJECT_DEFINITION(OBJECT_ID(NAME))%20AS%20xTriggerDefinitionx%20from%20sys.objects%20where%20type%20%3D'TR'%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EOPEN%20vcursor%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFETCH%20NEXT%20FROM%20vCursor%20INTO%20%40TRNAME%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHILE%20%40%40FETCH_STATUS%20%3D%200%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--PRINT%20%40TRNAME%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40N%20%3D%20charindex('raiserror%20%40errno%20%40errmsg'%2C%40TRname)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40Trname%20%3D%20REPLACE(%40TRname%2C'CREATE%20TRIGGER'%2C'ALTER%20TRIGGER')%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIf%20%40n%26lt%3B%26gt%3B%200%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40Trname%20%3D%20REPLACE(%40TRname%2C'raiserror%20%40errno%20%40errmsg'%2C'raiserror(%40errno%2C-1%2C-1%2C%20%40errmsg)')%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EPRINT%20%40TRNAME%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFETCH%20NEXT%20FROM%20vCursor%20INTO%20%40TRNAME%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECLOSE%20vcursor%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDEALLOCATE%20vcursor%3B%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Enjoy!%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-368879%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Apr%2010%2C%202017%20Today%2C%20we%20have%20been%20working%20a%20very%20interesting%20case%2C%20when%20our%20customer%20is%20migrating%20their%20database%20to%20Azure%20SQL%20Database.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-368879%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20SQL%20Database%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eobject_definition%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eraiserror%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Etrigger%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 13 2019 06:40 PM
Updated by: