Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SAVEPOINTS IN TRIGGERS Expand / Collapse
Author
Message
Posted Thursday, October 10, 2013 4:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, November 30, 2013 2:38 PM
Points: 15, Visits: 57
Hello 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
ALTER TRIGGER [dbo].[tr_ComponentOnLines] ON [dbo].[tblCFGLine] AFTER INSERT, UPDATE, DELETE
AS
BEGIN

BEGIN 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>0)
BEGIN
ROLLBACK TRAN TR
print @@TRANCOUNT
END
END CATCH
END

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?

Thanks
Post #1503463
Posted Thursday, October 10, 2013 5:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, November 30, 2013 2:38 PM
Points: 15, Visits: 57
After further research it seems that a solution could be to COMMIT the trigger in its very beginning , i.e

ALTER TRIGGER [dbo].[tr_ComponentOnLines] ON [dbo].[tblCFGLine] AFTER INSERT, UPDATE, DELETE
AS
BEGIN
COMMIT
BEGIN 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>0)
BEGIN
ROLLBACK TRAN TR
RETURN
END
END CATCH
END

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.
Post #1503489
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse