Triggers :

-> Trigger is a piece of code which will be executed in response to a DML statements or DDL statements. -> They are usually used to audit logging. -> Triggers are usefull in evaluating complex business logic before doing any DML/DDL operation. Triggers are classified into 2 types * DML triggers * DDL triggers DML Triggers: These triggers will be executed automatically when you perform a DML operation on a table or on a view. Note:By using triggers we can check complex business logic when compared to check constraint before doing modifications in the table data.

Types of DML Triggers :

*For triggers *After triggers *Instead of triggers

After triggers

-> after triggers are executed after the action of the INSERT,UPDATE or DELETE statement is performed. -> Specifying after is same as for, which was the only option available in earlier versions of Microsoft SQL server. -> Triggers can be specified only on tables and not on the views. Note:A table can have multiple after triggers provided the names of the triggers are different.

Instead of trigger

-> Instead of triggers are executed in place of the usual triggering action. -> Instead of triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support. -> we can create only one instead of trigger for each DML action on a table or view. Note -> We are not allowed to call the triggers explicitly instead database engine will call triggers based on the DML/DDL operations. -> In general a trigger reduces the performance when compared to contraints like check,default and foreign key constraint.

Difference between after triggers and instead of triggers :

-> Instead of triggers will be executed before executing the actual query. -> After trigger will be executed after executing the actual query. -> performance of the instead of triggers is better compared to the after triggers. Note In case of after triggers supplied data will go into the original source table and then trigger code will be executed based on commit or rollback performed in the trigger either data present in the source table wil be commited or rolled back. In case of instead of triggers trigger code will be executed first against the inserted or deleted table data and based on the commit or rollback action source data may b be sent to original table or data may be ignored. Syntax: create trigger on For|after|instead of {insert|update|delete} As t-sqlstatement 1.. t-sqlstatement 2.. Note -> When you are executing a DML statement sql server automatically creates two tables. -> All the DML operations are reversible. -> DBE will create inserted table in log data file when you are performing the insert operation on the source table. -> DBE will create deleted table in the ldf file when you are performing the delete operation on the source table. -> DBE will create inserted and deleted tables in the ldf file when you are performing update operation on the source table.(update operation is usually a combination of successive delete and insert operation) -> Inserted and deleted tables which are created by the DBE is having same structure as original table or source table. -> We are allowed to accesss inserted as well as deleted table only from the trigger code. -> In T-SQL language every DML opertion is considered as an transaction.

Trigger Code :

Ex: create trigger In:employee_sal_mustbe_lessthan_CEO on employees instead of insert as declare @compsal int, select @compsal=salary from inserted, declare @ceosal int, select @ceosal=salary from employees where role='ceo' if(@compsal=@ceosal) begin rollback transaction end Query: Write a trigger for not allowing the deletion of least paid employee Create trigger avoid_leastpaidET_deletion on employees instead of delete as declare @minsal int, select @minsal=min(salary) from employees, delete @csal=salary from deleted, if(@csal=@minsal) begin rollback transaction end.