Triggers

      Triggers are stored sub-programs that will be automatically executed based on specified event.

Differences between Stored Procedures, User Defined Functions and Triggers are as follows-->

• Stored Procedures and User Defined Functions can be called by the user manually but triggers cannot be called by the user manually and they will be automatically invoked.
• Stored Procedures can return a value with output parameters and User Defined Functions can return a value with return statement but triggers cannot return a value either with output parameters or return statement.
• Stored Procedures, User Defined Functions can take arguments but triggers cannot take arguments.

Based on event specified on a trigger, triggers are classified into DDL triggers and DML triggers. When the trigger is created by specifying a DDL command as event then that trigger is called as DDL triggers and when a trigger is created by specifying a DML command then that trigger is called as DML triggers. 

     

      DDL triggers are new in SQL 2005. DML triggers have the following three purposes-->

• Create procedure integrity constraints.
• Record auditing information of a table.
• Allow insert,update,delete on complex views.