Trigger:Trigger is same like a stored procedures where we can writeSQL code and save it which gets executed automatically whenever an event takesplace on the associated database.
Wecannot call triggers directly. There are 2 steps that are required for writinga trigger.1. Firstly we must mention when the trigger has tobe executed like. Such that this is considered as an event and checks thetrigger and whether it satisfies the given condition for which the trigger hasto execute.
2. Now we have to mention the actions that has totake place when the trigger is executed.Uses of Triggers:1. Triggers are used to maintain the complexintegrity constraints which are not possible at the time of table creation.
2. Automatically signalling the other programs thatan action needs to be take place when changes are made to a table.3. If we want to implement any particular businessrules then triggers are used in thatcase. Types of Triggers:1. DML Triggers2. DDL Triggers 3.
Logon TriggersDML triggers:DML triggers will be executed automatically whenever a datamanipulation operations (insertion, deletion, updating) take place on the tableor view which is mentioned in the trigger. DML triggers are classified into twotypes.1.
After Trigger2. INSTEAD OF triggerAfter Trigger: These types of triggers are fired only afterthe execution of DML operations and before the committing the data into thedatabase. We cannot apply after triggeron views. For example: Create an after trigger for insert operation. For table employee which has columns empno,empfirstname, emplastname, deptno, experience.
USE testingGO CREATE TRIGGER inserttrigeer ON employee AFTER INSERTAS declare @IDint set @ID = (select experience from inserted)BEGINif(@ID <5)begin print ' Experience should be greaterthan 5' rollbackend ENDGONow if you execute insert statementinsert into employee values (2,'abc','kil','p2',8) Then after the execution the after trigger is fired beforethe insert statement is committed and checks weather the experience is greater than5 or not else it will print the message and rollback the transaction. INSTEADOF trigger: INSTEAD OF trigger is executed in placeof insert, delete, and update. The actual insert, delete and update are notincluded in the table. For example: suppose if you have INSTEAD of trigger forinsert operation. USE testingGO CREATE TRIGGER inserttrigeer ON employee INSTEAD OF INSERTAS declare @IDint set @ID = (select experience from inserted)BEGINif(@ID >5)begin print ‘ Experience is greater than 5’end ENDGOThen if we give an insert statementinsert into employee values (4,’abc’,’kil’,’p2′,8) so before the insertstatement is executed the instead oftrigger for insert is raised and it will execute in place of insert operationand gives output as experience is greater than 5 and when you check the tablethe row will not inserted. Uses of DML Triggers:1. DML triggers are same as constraints whichenforce the data integrity.
2. A normal constraint in the SQL gives a standardsystem error message when an issue occurs. In order to customize the messageand to handle more complex error-handling DML triggers are used.3. To perform multiple actions for the same eventwe can create more than one trigger using DML triggers.DDL Triggers:These type of triggers are fired when any of the DDLstatements are executed i.e.
; DDL events occurred like when creation,alteration, granting permission, denning permissions, dropping, Revokepermissions. The scope of the DDL triggers:We can create the DDL triggers in a particular database orat the server level. So when you create a DDL trigger we must specify whetherit is database level or server level by ON DATABASE clause or ON ALL SERVERclause respective. Database level meanswhen creating a table, procedure, functions and altering a table, procedure andfunction in a database. Server level means when creating a login, database,altering the database, login and dropping the login, granting the server etc. Unlike the DML triggers DDL triggers doesn’t contain theINSTEAD OF trigger. DDL trigger will not be executed if an event occurs suchthat it has an impact on global temporary tables and procedures.
Uses of DDL Triggers:1. By using DDL triggers we can place restrictionson a user’s who are going to attempt to create, alter, and drop the databaseobjects. 2. Used to record the changes that take place inthe database schema. Example:Create a database scope DDL triggeruse testingGOCREATE TRIGGER droptestingON DATABASEFOR DROP_TABLEASPRINT ‘table cannot bedropped’ROLLBACK Now when we drop the table then the trigger will fire andprint the message table cannot be dropped and then drop command will rollbackand it doesn’t executed.