Microsoft SQL Server Trigger DML Triggers
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.
