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, May 10 '16 at 21:10
Possible duplicate of [INSERT deleted values into a table before DELETE with a DELETE TRIGGER](http://stackoverflow.com/questions/14018982/insert-deleted-values-into-a-table-before-delete-with-a-delete-trigger) — dfundako, May 10 '16 at 21:11

score 1 · Accepted Answer · answered May 11 '16 at 05:24

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 Answers1