Trigger in Sql

A trigger is a special kind of stored procedure, that is executed automatically when a specified event fired on database server.

There are three types of triggers DML trigger, DDL trigger and Logon trigger.
1. DML trigger - A DML trigger can be created on a table or view, its executes when a user tries to update data through DML operations (insert / update / delete), trigger fires regardless of whether or not data is updated.
DML triggers have two types of nature 'After' and 'Instead of'
After - Trigger with after fires after execution of specified event for that triger is created. We use keyword FOR or AFTER for it, both are same.
Instead Of - Trigger with instead of option fires instead of executing specified event. Trigger of this nature overrides specified event or triggering statement with statements defined in body of trigger.
Instead of option can't be used with DDL or Logon triggers.
Syntax - 

CREATE TRIGGER trigger_name
ON { table | view }
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement }

Ex. This trigger enforces a business rule when user try to insert an employee record in employee table, that protect the table from inserting bonus out of the range that company has specified.

create trigger trgEmpSalary
on tbl_employee
for Insert
AS
begin
if exists (select * from tbl_employee where bonus between 1000 and 10000)
begin
raiserror('Bonus range in company is 1000 to 10000',10,1)
rollback transaction
end
end


2. DDL trigger - A DDL trigger can be created on all servers or on database, its executes in response to DDL events like create, drop, alter, grant, revoke, deny.
Syntax - 
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement }

3. Logon trigger - A logon trigger can be created on all servers, its executes when a new user session establishes.
Syntax - 
CREATE TRIGGER trigger_name
ON ALL SERVER
{ FOR| AFTER } LOGON
AS { sql_statement }


General considerations

1. Returning Results - Applications those are not designed to handle returned result set by trigger can show unaspected behavior. So that in future versions of sql server this nature of triggers will be removed. By default triggers can return result set, to disable this behavior, disable 'disallow results' option by using sp_configure. Logon triggers do not return result.

2. Multiple Triggers - For same object and same event we can define multiple DDL, DML, and logon triggers. For example we can create more than one triggers for update event on employee table.

3. Recursive Triggers - Direct recursion - If application updates table employee, then trigger TR_employee for update event will be fire, the trigger updates employee table then again trigger will be fire, and so on. Indirect Recursion - If application update employee table trigger for update event TR_employee will be fire, TR_employee updates table order, then trigger TR_order will be fire, TR_order updates table employee then again trigger TR_employee will be fire, and so on. Using 'ALTER DATABASE' direct recursion can be disabled, but to disable indirect recursion, need to set 'nested triggers' server option to 0. If any trigger executes ROLLBACK TRANSACTION then no more triggers will be execute.

4. Nested triggers - Triigers can be nested upto 32 levels. Recursive triggers are also nested triggers. Its not neccessary that nested triggers have a cyclic chain. Nesting of triggers can be disabld by setting 'nested triggers' server option to 0, can be set by using sp_configure.
To create a DML trigger requires ALTER permission on the table or view on which the trigger is being created. To create a DDL trigger with server scope (ON ALL SERVER) or a logon trigger requires CONTROL SERVER permission on the server. To create a DDL trigger with database scope (ON DATABASE) requires ALTER ANY DATABASE DDL TRIGGER permission in the current database.