Delete trigger to insert entry into table

Question

Created a Customers_Audit table in my DEV_SQL database with the columns:

[AuditID] int IDENTITY
[CustomerID] nchar(5)
[UserName] nvarchar(50)
[DeleteDate] smalldatetime

I am trying to write a delete trigger to insert an entry to the Customers_Audit table every time a row is deleted from the Customers table, need some help oh wise ones!

You can literally copy/paste the name of your post into Google and the answer is in the first 3 results. — dfundako
Possible duplicate of INSERT deleted values into a table before DELETE with a DELETE TRIGGER — dfundako

marc_s marc_s · Accepted Answer · 2016-05-11T05:24:05

You need something like this - just insert the rows into Customers_Audit in your AFTER DELETE trigger and set the fixed values as needed:

CREATE TRIGGER trgAfterDelete 
ON dbo.Customer
AFTER DELETE
AS 
    -- insert into the audit table - explicitly specifying the column names to use
    INSERT INTO dbo.Customers_Audit(CustomerId, UserName, DeleteDate)
        -- make sure to respect the fact that the "Deleted" table might
        -- contain *multiple* rows if your DELETE statement deleted 
        -- more than a single row - use proper set-based code!
        SELECT
            d.CustomerId, d.UserName, SYSDATETIME()
        FROM 
            Deleted d

Delete trigger to insert entry into table

1 Answers