October 10, 2013 at 4:51 am
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
October 10, 2013 at 5:52 am
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply