Triggers are special kind of stored procedures that triggers automatically when an event occur. Triggers are written to fire when a specific event on table occurs. Following are the events that may force trigger-:
1) DML operations
Database manipulation operations like INSERT, UPDATE and DELETE.
2) DDL operations
Database definition operations like CREATE, ALTER and DROP.
3) Database operations
Like LOGON, LOGOFF, STARTUP, or SHUTDOWN.
Syntax of DML trigger
CREATE TRIGGER Trigger_Name
A simple example to show a DML trigger
Here, I am going to create a trigger that will fire after a row was inserted in a table.
Below is the employee table for which I will create a trigger.
Example of a DML trigger
CREATE TRIGGER InsertInEmployee
AFTER INSERT -- This will fire after insert on employee
DECLARE @Age INT;
SET @Age = (SELECT Age FROM INSERTED AS i) -- INSERTED will keep the new row inserted
IF(@Age > 60)
RAISERROR ('Employee over 60 not allowed.', 16, 1);
Here, I have written a trigger for employee which will be fired when a new record is being inserted. This trigger will not allow to insert a record with age greater than 60.