CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
user20881100
user20881100

Reputation:

SQL Server Trigger how to log all actions

I have some triggers which are used to duplicate data to another DB which have a different structure.

I need to do something like:

BEGIN TRY
if exists
UPDATE db2 set fld = INSERTED.fld

if not exists
INSERT INTO db2 ...
INSERT INTO log (text) values ('row inserted blabla'+:inserted.ID)

END TRY
TRY CATCH
INSERT INTO log (text) values ('insert failed blabla'+:ERROR_MESSAGE())
END CATCH;

First, I used cursors because for each row I need to INSERT or UPDATE the 2nd DB and I'm using TRY..CATCH for errors. It's working but everyone's saying I should not use cursor for performance issues.

Second, I used dynamic SQL which write all my queries into a VARCHAR(MAX) which I execute after I passed on every records it's taking like 100 times.

Something like:

DECLARE @SQL VARCHAR(MAX) = '';
SELECT @SQL = @SQL+ 'BEGIN TRY etc.....'+'END TRY'+'BEGIN CATCH INSERT INTO  INSERT INTO log (text) values ('''insert failed blabla'''+:ERROR_MESSAGE()+')END CATCH;' 
FROM INSERTED
EXECUTE (@SQL)

At first I was thinking about using a MERGE statement but my need is to track every records update or insert so the OUTPUT instruction is not enough because I need to logs stuff like the value of @@IDENTITY

What is the best way to approach this problem?

Upvotes: 0

Views: 592

Answers (1)

Tim Jarosz
Tim Jarosz

Reputation: 1168

Try using the OUTPUT clause. From the documentation the OUTPUT works even if there is a rollback, so you might need more error checking. Here's a quick example from your pseudo code:

BEGIN TRY
  
if exists
BEGIN
  UPDATE db2 set fld = INSERTED.fld
    OUTPUT INSERTED.ID, INSERTED.Name, INSERTED.ModifiedDate  
        INTO @UpdateResults;

  INSERT INTO log(text)
  SELECT 'row updtaed blabla' + CAST(ID as nvarchar)
  FROM @UpdateResults;
END
if not exists
BEGIN
  INSERT INTO db2 ...
  OUTPUT INSERTED.ID, INSERTED.Name, INSERTED.ModifiedDate  
        INTO @InsertResults;

  INSERT INTO log(text)
  SELECT 'row inserted blabla' + CAST(ID as nvarchar)
  FROM @InsertResults;
END
  
END TRY
TRY CATCH
INSERT INTO log (text) values ('insert failed blabla'+:ERROR_MESSAGE())
END CATCH;

Upvotes: 2

Related Questions