Trigger:Trigger is same like a stored procedures where we can write SQL code and save it which gets executed automatically whenever an event takes place on the associated database.
We cannot call triggers directly. There are 2 steps that are required for writing a trigger.1. Firstly we must mention when the trigger has to be executed like. Such that this is considered as an event and checks the trigger and whether it satisfies the given condition for which the trigger has to execute.2. Now we have to mention the actions that has to take place when the trigger is executed.Uses of Triggers:1.
Triggers are used to maintain the complex integrity constraints which are not possible at the time of table creation.2. Automatically signalling the other programs that an action needs to be take place when changes are made to a table.
3. If we want to implement any particular business rules then triggers are used in that case. Types of Triggers:1.
DML Triggers2. DDL Triggers 3. Logon TriggersDML triggers:DML triggers will be executed automatically whenever a data manipulation operations (insertion, deletion, updating) take place on the table or view which is mentioned in the trigger.
DML triggers are classified into two types.1. After Trigger2. INSTEAD OF triggerAfter Trigger: These types of triggers are fired only after the execution of DML operations and before the committing the data into the database. We cannot apply after trigger on views. For example: Create an after trigger for insert operation.
For table employee which has columns empno, empfirstname, emplastname, deptno, experience. USE testingGOCREATE TRIGGER inserttrigeer ON employee AFTER INSERTAS declare @ID int set @ID = (select experience from inserted)BEGINif(@ID <5)begin print ' Experience should be greater than 5' rollbackendENDGONow if you execute insert statementinsert into employee values (2,'abc','kil','p2',8) Then after the execution the after trigger is fired before the insert statement is committed and checks weather the experience is greater than 5 or not else it will print the message and rollback the transaction. INSTEADOF trigger: INSTEAD OF trigger is executed in place of insert, delete, and update statements. The actual insert, delete and update are not saved into the table. For example: suppose if you have INSTEAD of trigger for insert operation. USE testingGOCREATE TRIGGER inserttrigeer ON employee INSTEAD OF INSERTAS declare @ID int set @ID = (select experience from inserted)BEGINif(@ID >5)begin print ‘ Experience is greater than 5’endENDGOThen if we give an insert statementinsert into employee values (4,’abc’,’kil’,’p2′,8) so before the insert statement is executed the instead of trigger for insert is raised and it will execute in place of insert operation and gives output as experience is greater than 5 and when you check the table the row will not inserted. Uses of DML Triggers:1.
DML triggers are same as constraints which enforce the data integrity.2. A normal constraint in the SQL gives a standard system error message when an issue occurs. In order to customize the message and to handle more complex error-handling DML triggers are used.3.
To perform multiple actions for the same event we can create more than one trigger using DML triggers.DDL Triggers:These type of triggers are fired when any of the DDL statements are executed i.e.; DDL events occurred like when creation, alteration, granting permission, denning permissions, dropping, Revoke permissions.
The scope of the DDL triggers:We can create the DDL triggers in a particular database or at the server level. So when you create a DDL trigger we must specify whether it is database level or server level by ON DATABASE clause or ON ALL SERVER clause respective. Database level means when creating a table, procedure, functions and altering a table, procedure and function 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 the INSTEAD OF trigger we can use only FOR/AFTER clause. DDL trigger will not be executed if an event occurs such that it has an impact on global temporary tables and procedures.Uses of DDL Triggers:1. By using DDL triggers we can place restrictions on a user’s who are going to attempt to create, alter, and drop the database objects. 2. Used to record the changes that take place in the database schema.
Example:Create a database scope DDL triggeruse testingGOCREATE TRIGGER droptestingON DATABASEFOR DROP_TABLEASPRINT ‘table cannot be dropped’ROLLBACKNow when we drop the table then the trigger will fire and print the message table cannot be dropped and then drop command will rollback and it doesn’t executed. Logon Triggers:These triggers are the special type of triggers which will fire before a user session is established. Which will happen after the SQL server authentication is successful. So all the messages that we wrote in trigger will be switch to SQL server error log.
These are used to control the audit sessions.