CODEWITHSUNDEEP
t-sqlsql-server-2008
TLD
TLD

Reputation: 8135

Trigger that logs changes on every INSERT, DELETE

I tried create a trigger:

CREATE TRIGGER DataTrigger ON Data AFTER INSERT , DELETE
AS
BEGIN
IF EXISTS(SELECT * FROM Inserted)

INSERT INTO [dbo].[AuditTrail]
       ([ActionType]
       ,[TableName]
       ,[Name]
       ,[Time])

('INSERT'   //Incorrect syntax near 'INSERT'
    ,'Data' 
    ,SELECT col1 FROM Inserted
    ,CURRENT_TIMESTAMP  //Incorrect syntax near the keyword 'CURRENT_TIMESTAMP')
END

but it keep saying that I have thoes errors, can somebody show me where I did wrong?
P/S: what is the best way to detect an update? Thank you

Upvotes: 1

Views: 9578

Answers (3)

Chris Diver
Chris Diver

Reputation: 19802

CREATE TRIGGER DataTrigger ON Data AFTER INSERT , DELETE
AS
BEGIN    
  INSERT INTO [dbo].[AuditTrail]
       ([ActionType]
       ,[TableName]
       ,[Name]
       ,[Time])
  SELECT 'INSERT'  
    ,'Data' 
    ,col1
    ,CURRENT_TIMESTAMP
  FROM Inserted

END

Just for clarification.

Your query, if syntatically correct would have failed if more than one row was inserted, the version above allows for multiple inserts.

The IF EXISTS was redundant, which is why it was removed, if there are no rows there will be no insert into your audit table.

If you want to audit DELETE you'll need a similar statement again, but using the Deleted table rather than Inserted

To audit UPDATE, create a new trigger, for each updated row you get an entry in Inserted with the new updates and an entry in Deleted with the old data, you can join these if you want to track old and new.

Upvotes: 4

David Steele
David Steele

Reputation: 3461

I am not entirely sure what you are trying to accomplish here so I have just corrected the syntax to get it to work, but it should help. I have also tried to handle the deleted case as well

CREATE TRIGGER DataTrigger 

ON Data 

AFTER INSERT , DELETE 
AS
BEGIN


    INSERT INTO [dbo].[AuditTrail]([ActionType],       
                                             [TableName],       
                                             [Name],       
                                             [Time])
    SELECT 'INSERT','Data', col1, CURRENT_TIMESTAMP
    FROM Inserted


    INSERT INTO [dbo].[AuditTrail]([ActionType],       
                                             [TableName],       
                                             [Name],       
                                             [Time])
    SELECT 'DELETE','Data', col1, CURRENT_TIMESTAMP
    FROM Deleted

END

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300489

CREATE TRIGGER DataTrigger ON Data AFTER INSERT , DELETE
AS
BEGIN

INSERT INTO [dbo].[AuditTrail]
       ([ActionType]
       ,[TableName]
       ,[Name]
       ,[Time])
SELECT 'INSERT', 'Data', col1, CURRENT_TIMESTAMP FROM Inserted

END

Upvotes: 3

Related Questions