Creating SQL Triggers

A trigger is a block of SQL statements that are automatically executed whenever data in underlying table is affected by DML statements - INSERT, UPDATE or DELETE.

Triggers help in maintaining consistent, reliable, and correct data in tables. It cannot be explicitly invoked or executed, as in the case of the stored procedures.

Whenever a trigger fires in response to the INSERT, UPDATE, or DELETE statement, two special tables are created. These are the inserted and the deleted tables. They are also referred to as the magic tables. These are similar in structure to the trigger table.
The inserted table contains a copy of all records that are inserted in the trigger table.
The deleted table contains all records that have been deleted from the trigger table.
Whenever an update takes place, the trigger uses both the inserted and deleted tables.

Syntax
CREATE TRIGGER trigger_name
ON table_name
[WITH ENCRYPTION]
AFTER | INSTEAD OF
INSERT | UPDATE | DELETE
AS
BEGIN
SQL Statement 1
SQL Statement 2
...
END

where,
WITH ENCRYPTION - Obfuscates the text of the CREATE TRIGGER statement.
AFTER - fired only when all operations specified in the triggering SQL statement have executed successfully.
INSTEAD OF - trigger is executed instead of the triggering SQL statement.

Enable/Disable a trigger
ENABLE | DISABLE TRIGGER trigger_name ON table_name

Dropping a trigger
DROP TRIGGER trigger_name

Example
CREATE TRIGGER trgUserDelete
ON Employee
AFTER DELETE
AS
BEGIN
DECLARE @UserType AS VARCHAR(MAX)
SELECT @UserType = UserType FROM DELETED

IF (@UserType == 'Admin')
BEGIN
PRINT 'User of type Admin cannot be deleted'
ROLLBACK
END
ELSE
BEGIN
PRINT 'User deleted'
END
END