Luke Hill Luke Hill -4 years ago 70
SQL Question

Creating a trigger to count how many times a piece of data has been changed SQL

I am currently writing an SQL trigger that records how many times a piece of data has been changed. It will write it to a column 'PrevAddrNum' in tblCustomer, the trigger executes fine but on testing I got the error message saying "The transaction ended in the trigger the batch has been aborted". Here is my code below, any help would be greatly appreciated!

CREATE TRIGGER PrevAddr ON tblCustomer
AFTER UPDATE
AS
IF UPDATE(CustomerAddress)
BEGIN
UPDATE tblCustomer
SET PrevAddrNum = PrevAddrNum + 1
END

Answer Source

Assuming your table has a CustomerID or similar field, you should update only affected records.

CREATE TRIGGER PrevAddr ON tblCustomer
AFTER UPDATE
AS
BEGIN TRY
  UPDATE     tblCustomer
  SET        PrevAddrNum = PrevAddrNum + 1
  FROM       tblCustomer tc
  INNER JOIN deleted 
  ON         tc.CustomerID = deleted.CustomerID
  INNER JOIN inserted 
  ON         tc.CustomerID = inserted.CustomerID
  WHERE      deleted.CustomerAddress <> inserted.CustomerAddress
END TRY
BEGIN CATCH
    -- just to get the error 
    -- create a new table ErrorLog and save the errors there
    DECLARE @ErrorMsg VARCHAR(MAX), @ErrorNumber INT, @ErrorProc sysname, ErrorLine INT 

    SELECT @ErrorMsg = ERROR_MESSAGE(), @ErrorNumber = ERROR_NUMBER(), 
           @ErrorProc = ERROR_PROCEDURE(), @ErrorLine = ERROR_LINE();

    INSERT INTO ErrorLog (ErrorMsg,  ErrorNumber,  ErrorProc,  ErrorLine)
    VALUES               (@ErrorMsg, @ErrorNumber, @ErrorProc, @ErrorLine)
END CATH
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download