SQLServerCentral.com / SQL Server 7,2000 / T-SQL / SAVEPOINTS IN TRIGGERS / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 31 Mar 2015 15:43:20 GMT20RE: SAVEPOINTS IN TRIGGERShttp://www.sqlservercentral.com/Forums/Topic1503463-8-1.aspxAfter further research it seems that a solution could be to COMMIT the trigger in its very beginning , i.e [code="sql"]ALTER TRIGGER [dbo].[tr_ComponentOnLines] ON [dbo].[tblCFGLine] AFTER INSERT, UPDATE, DELETEAS[b]BEGIN[/b]COMMITBEGIN TRY DECLARE @temptblInsertUpdate TABLE ( ID INT, Line VARCHAR(20), LineTypeID INT, Activity VARCHAR(20) ); DECLARE @Activity AS VARCHAR(20); DECLARE @RowCounter AS INT INSERT INTO @temptblInsertUpdate (ID,Line,LineTypeID,Activity) SELECT I.LineID,I.LineDesc,(SELECT LineTypeID FROM DhubOEE.dbo.LineType WHERE OlympusID=I.LineTypeID),CASE WHEN EXISTS(SELECT * FROM deleted) THEN 'UPDATE' ELSE 'INSERT' END AS Activity FROM INSERTED I UNION ALL SELECT D.LineID,D.LineDesc,(SELECT LineTypeID FROM DhubOEE.dbo.LineType WHERE OlympusID=D.LineTypeID),'DELETE' FROM DELETED D WHERE NOT EXISTS (SELECT * FROM INSERTED) --Check if the Line already exists in Assets. SET @ROWCOUNTER=( Select Count(*) FROM @temptblInsertUpdate Temp INNER JOIN DhubOEE.dbo.Asset A ON Temp.Line=A.AssetName ) SET @Activity=(SELECT TOP(1) Activity FROM @temptblInsertUpdate) SAVE TRANSACTION Tr --BEGIN TRAN DECLARE @ErrorValue INT=0 IF(@RowCounter=0) BEGIN --Create The Line INSERT INTO DhubOEE.dbo.Asset(ParentID,AssetName,Path) SELECT (Select TOP(1) AssetID from DhubOEE.dbo.Asset),temp.Line,NULL FROM @temptblInsertUpdate temp --Update the path of the newly created Asset UPDATE DhubOEE.dbo.Asset SET Path='.1.'+CONVERT(VARCHAR(MAX),SCOPE_IDENTITY()) WHERE AssetID=SCOPE_IDENTITY() --Construct the path by updating Line INSERT INTO DhubOEE.dbo.Line (OlympusID,Linedesc,LineTypeID,State,AssetID) SELECT 30,Asset.AssetName,LineTypeID,1,SCOPE_IDENTITY() FROM DhubOEE.dbo.Asset INNER JOIN @temptblInsertUpdate Temp ON Temp.Line=Asset.AssetName END IF @Activity='UPDATE' BEGIN UPDATE L SET L.LineDesc=Temp.Line,L.LineTypeID=Temp.LineTypeID FROM DhubOEE.dbo.Line L INNER JOIN @temptblInsertUpdate AS Temp ON L.OlympusID=Temp.ID END IF @Activity='DELETE' BEGIN UPDATE L SET L.State=0 FROM DhubOEE.dbo.Line L INNER JOIN @temptblInsertUpdate AS Temp ON L.OlympusID=Temp.ID END SET @ErrorValue=@@ERROR --COMMIT END TRY BEGIN CATCH IF (@ErrorValue&gt;0) BEGIN ROLLBACK TRAN TR RETURN END END CATCHEND[/code]Additionally, there is no need for the second transaction. However, I have read that even though COMMIT in the beginning of a trigger could be a solution, it is an ugly one. I still don't understand why the savepoints can't work. I am building a system that uses triggers for data change capture. I know that CDC is the right solution, but the client has SQL SERVER 2008 EXPRESS, so I am stuck with the triggers.For now , I have solved the issue but it's an ugly solution and I don't like ugly stuff! I am still trying to understand why savepoints didn't work.Thu, 10 Oct 2013 05:52:26 GMTstergiazotaliSAVEPOINTS IN TRIGGERShttp://www.sqlservercentral.com/Forums/Topic1503463-8-1.aspxHello all,Like always, your ideas/suggestions will be greatly appreciated.I have created a trigger. The idea is that when there is any DML operation on Table A, changes should be reflected on Table B. So far so good.Before I continue, here is the code[code="sql"]ALTER TRIGGER [dbo].[tr_ComponentOnLines] ON [dbo].[tblCFGLine] AFTER INSERT, UPDATE, DELETEASBEGINBEGIN TRY DECLARE @temptblInsertUpdate TABLE ( ID INT, Line VARCHAR(20), LineTypeID INT, Activity VARCHAR(20) ); DECLARE @Activity AS VARCHAR(20); DECLARE @RowCounter AS INT INSERT INTO @temptblInsertUpdate (ID,Line,LineTypeID,Activity) SELECT I.LineID,I.LineDesc,(SELECT LineTypeID FROM DhubOEE.dbo.LineType WHERE OlympusID=I.LineTypeID),CASE WHEN EXISTS(SELECT * FROM deleted) THEN 'UPDATE' ELSE 'INSERT' END AS Activity FROM INSERTED I UNION ALL SELECT D.LineID,D.LineDesc,(SELECT LineTypeID FROM DhubOEE.dbo.LineType WHERE OlympusID=D.LineTypeID),'DELETE' FROM DELETED D WHERE NOT EXISTS (SELECT * FROM INSERTED) --Check if the Line already exists in Assets. SET @ROWCOUNTER=( Select Count(*) FROM @temptblInsertUpdate Temp INNER JOIN DhubOEE.dbo.Asset A ON Temp.Line=A.AssetName ) SET @Activity=(SELECT TOP(1) Activity FROM @temptblInsertUpdate) SAVE TRANSACTION Tr BEGIN TRAN DECLARE @ErrorValue INT=0 IF(@RowCounter=0) BEGIN --Create The Line INSERT INTO DhubOEE.dbo.Asset(ParentID,AssetName,Path) SELECT (Select TOP(1) AssetID from DhubOEE.dbo.Asset),temp.Line,NULL FROM @temptblInsertUpdate temp --Update the path of the newly created Asset UPDATE DhubOEE.dbo.Asset SET Path='.1.'+CONVERT(VARCHAR(MAX),SCOPE_IDENTITY()) WHERE AssetID=SCOPE_IDENTITY() --Construct the path by updating Line INSERT INTO DhubOEE.dbo.Line (OlympusID,Linedesc,LineTypeID,State,AssetID) SELECT 30,Asset.AssetName,LineTypeID,1,SCOPE_IDENTITY() FROM DhubOEE.dbo.Asset INNER JOIN @temptblInsertUpdate Temp ON Temp.Line=Asset.AssetName END IF @Activity='UPDATE' BEGIN UPDATE L SET L.LineDesc=Temp.Line,L.LineTypeID=Temp.LineTypeID FROM DhubOEE.dbo.Line L INNER JOIN @temptblInsertUpdate AS Temp ON L.OlympusID=Temp.ID END IF @Activity='DELETE' BEGIN UPDATE L SET L.State=0 FROM DhubOEE.dbo.Line L INNER JOIN @temptblInsertUpdate AS Temp ON L.OlympusID=Temp.ID END SET @ErrorValue=@@ERROR COMMIT END TRY BEGIN CATCH IF (@ErrorValue&gt;0) BEGIN ROLLBACK TRAN TR print @@TRANCOUNT END END CATCHEND[/code]The task is that even if smtg happens during the operations on the Table B i.e foreign key violation), the operations on Table A should continue and complete. The problem is that if smtg goes wrong on table b , the operations on table A are aborted as well.I understand that when a trigger executes, an implicit transaction starts. Therefore, even if you create another transaction, inside your trigger, that would be considered as a nested transaction , THEREFORE, if an error occurs, theory says that the entire batch will rollback. I thought, that a way to battle this, is by bringing savepoints on board. However, there is smtg I am missing/doing wrong and it doesn't work and the entire set of operations is aborted.Any ideas?ThanksThu, 10 Oct 2013 04:51:42 GMTstergiazotali