Microsoft SQL Server Trigger DML Triggers

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Insert
> Step 2: And Like the video. BONUS: You can also share it!

Example

DML Triggers are fired as a response to dml statements (insert, update or delete).
A dml trigger can be created to address one or more dml events for a single table or view. This means that a single dml trigger can handle inserting, updating and deleting records from a specific table or view, but in can only handle data being changed on that single table or view.

DML Triggers provides access to inserted and deleted tables that holds information about the data that was / will be affected by the insert, update or delete statement that fired the trigger.

Note that DML triggers are statement based, not row based. This means that if the statement effected more then one row, the inserted or deleted tables will contain more then one row.

Examples:

CREATE TRIGGER tblSomething_InsertOrUpdate ON tblSomething  
FOR INSERT
AS

    INSERT INTO tblAudit (TableName, RecordId, Action)
    SELECT 'tblSomething', Id, 'Inserted'
    FROM Inserted

GO

CREATE TRIGGER tblSomething_InsertOrUpdate ON tblSomething  
FOR UPDATE
AS

    INSERT INTO tblAudit (TableName, RecordId, Action)
    SELECT 'tblSomething', Id, 'Updated'
    FROM Inserted 

GO

CREATE TRIGGER tblSomething_InsertOrUpdate ON tblSomething  
FOR DELETE
AS

    INSERT INTO tblAudit (TableName, RecordId, Action)
    SELECT 'tblSomething', Id, 'Deleted'
    FROM Deleted

GO

All the examples above will add records to tblAudit whenever a record is added, deleted or updated in tblSomething.



Got any Microsoft SQL Server Question?