A trigger is a stored procedure used to execute the statements when an event is raised. The trigger is used statements for actions like insert, delete or update the data. The basics about triggers in sql server is here. In this article we will discuss about several trigger types used for manipulating the data. The following trigger types are mentioned below: Data Modification Language ( DML ) triggers Data Definition Language ( DDL ) triggers Before we begin just an introduction from above article about how to create triggers. Creating Triggers User can create trigger using the CREATE TRIGGER statement. The syntax for creatinf a trigger is as shown below: Code: CREATE TRIGGER trigger_name ON { table | view } { FOR | AFTER | INSTEAD OF } [ WITH [ ENCRYPTION ] [ EXECUTE AS ] ] { AS { sql_statement [ …n ] } } Where, trigger_name is the name of the trigger to be created table | view specifies the name of the table or view on which the trigger is created. FOR | AFTER | INSTEAD OF specifies the execution of the trigger WITH ENCRYPTION encrypts the text of the CREATE TRIGGER statement EXECUTE AS specifies the context in which the trigger is created Consider the following example of creating a trigger in SQL. Code: create trigger tr1 on empholiday1 after update as begin update empholiday1 set empid = 202 from inserted where inserted.vacationhours=80; end In the above statement, the trigger as tr1 is created on the empholiday table. The trigger is fired when the UPDATE statement on the empholiday table. The trigger updates the vacation hours with the updated value. The following statement creates a trigger to display the data inserted in the magic tables. Code: create trigger trg2 on empholiday1 after update as begin select * from deleted select * from inserted end In the above statement an AFTER UPDATE trigger on the empholiday table is created. Consider an update statement to be executed on the empholiday table Code: update empholiday1 set empid=270 where empid=201 The following output is generated when the trigger is fired on the empholiday table. In the above output, the result on the top shows the values before the execution of the UPDATE statement and the lower result set shows the values after the values are updated. DML Triggers A DML Trigger is used when the DML statements as INSERT, UPDATE or DELETE are affecting the tables. The data is maintained in these tables. The characteristics of the DML trigger are as mentioned below: They are fired automatically by the SQL server when a modification statement is executed. It does not return any value to the user They cannot be explicitly invoked or executed It is used to prevent inconsistent changes to the data The SQL Server creates two temporary tables as magic tables. They are created when the INSERT, DELETE or UPDATE statement are executed. The tables are known as Inserted and Deleted. The structure is similar to the table on which the trigger is defined. The values in the Inserted table are the copy of the records inserted in the trigger table. The Deleted table contains the records that are deleted from the trigger table. Consider the examples of INSERT, UPDATE and DELETE triggers in SQL server. Insert Trigger in SQL The insert trigger is fired at the time of adding the new rows to the trigger table. Consider the creation of a trigger on the employeedata in the SQL Server. Code: create trigger trginsert on employeedata for insert as DECLARE @ContactID int select @ContactID=ContactID from inserted if(@ContactID != 1234) begin print 'The Contact ID is not correct' rollback transaction end In the above code, the trigger named as trginsert is created on the employeedata table. If the user wants to modify the contact id of the user, the data cannot be modified. Insert the following values in the employeedata table. Code: insert into employeedata values (701,3456,'sam', 'analyst'); The following statement is displayed when the trigger is raised by the server. UPDATE trigger in SQL An UPDATE trigger is fired when the user is updating the records in the trigger table. The following statement is used to create the update trigger on the examdata table. Code: create trigger trgupdateexam on examdata for update as if UPDATE (subid) begin DECLARE @subid int select @subid=201 from examdata if(@subid = 201) begin print 'The subname is Science' rollback transaction end end Update the values in the examdata table by using the following statement. Code: update examdata set subid = 208 where subname = 'Science' The following message is displayed when the update trigger is used by the server. Delete Trigger in SQL The DELETE Trigger is fired when the user deletes the rows from the trigger table. Consider an example for trigger creation to disable the deletion of rows from the salary table Code: CREATE TRIGGER trgdeletedata ON salary FOR DELETE AS PRINT ‘ Deletion of data is not possible’ ROLLBACK TRANSACTION RETURN AFTER TRIGGER in SQL The AFTER TRIGGER is fired when the DML statement is executed. The following statement is used to perform the AFTER TRIGGER execution. Code: CREATE TRIGGER trgdeletevalue ON salary AFTER DELETE AS PRINT ‘The deletion is successful’ When there are multiple AFTER Triggers for a DML operation, the sequence of the trigger execution can be changed. The sp_settriggerorder system stored procedure is used to change the sequence of the execution. Code: sp_settriggerorder <triggername>, <order-value>, <DML-operation> Where, triggername specifies the name of the trigger whose order of execution is to be modified. order-value is the order in which the trigger needs to be executed DML-operation is the operation for which the trigger is created. Consider the example for AFTER TRIGGER in SQL server. Code: CREATE TRIGGER trgDelete1 ON empholiday1 AFTER DELETE AS PRINT ‘Assuming to Delete’ The trigger is executed in the sequence in which they are created. To modify the sequence of the triggers, the following statement is executed. Code: sp_settriggerorder ‘trgDelete1’, ‘FIRST’, ‘DELETE’ RETURN INSTEAD OF TRIGGER in SQL The INSTEAD OF triggers are executed when the events causes the trigger to fire. If user wants to create the INSTEAD OF UPDATE trigger on a table, the statements in the trigger will be executed instead of the UPDATE statement. The triggers are executed after the inserted and deleted tables reflecting the changes to the base tables are created. They are executed before the constraint is executed by the server. Consider the example of restricting the INSTEAD OF trigger on the deletion of records in the Employee table. Code: CREATE TRIGGER trgDelete2 ON Employee INSTEAD OF DELETE AS PRINT ‘The employee records cannot be deleted’ DDL Trigger The DDL Trigger is used to fire when the DDL statements are executed by the user. The administrative tasks are performed by the user for regulating the database operations. The following statement creates a DDL trigger. Code: CREATE TRIGGER trgperform1 ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT ‘User must disable the trigger’ ROLLBACK The above trigger will be fired when the DROP_TABLE or ALTER_TABLE event occurs on the database. Managing Triggers User can manage the triggers created in the system. The following operations are performed on the triggers. Altering a Trigger There are requirements when user needs to modify the data added in the trigger. To modify the trigger, user needs to change the code of the trigger. The ALTER TRIGGER is used for modifying the trigger. The syntax for ALTER TRIGGER is as shown below. Code: ALTER TRIGGER trigger_name { FOR | AFTER | INSTEAD OF } { event_type [ ,…n ] | DDL_DATABASE_LEVEL_EVENTS ) { AS { sql_statement [ ,…. n ] } } Consider an example for modifying the trigger trgdelete2 that was created to delete the records from the table. Execute the following statement to modify the trigger. Code: ALTER TRIGGER trgDelete2 ON Employee INSTEAD OF DELETE AS PRINT ‘Deletion of records is not allowed’ ROLLBACK TRANSACTION RETURN Deleting a Trigger There might be certain requirements where user wants to delete some triggers. The DROP TRIGGER statement is used for deleting a trigger. The syntax for DROP TRIGGER is as shown below: Code: DROP TRIGGER { trigger } Where, trigger is the name of the trigger The following statement is used to drop the trigger. Code: DROP TRIGGER trgDatavalues1 Disabling a Trigger User needs to restrict the execution of a trigger to perform certain operations on a table. When the user disables a trigger, it will not execute until it is enabled again. The trigger is not deleted but it is present in the database. The DISABLE TRIGGER is used to disable the trigger on the table. The syntax for disabling the trigger is as shown below. Code: DISABLE TRIGGER { [ schema_name ]. trigger_name | ALL } ON { object_name | DATABASE } Where, schema_name is the name of the schema trigger_name is the name of the trigger ALL is used to specify the triggers defined on the clause object_name is the name of the table DATABASE is the one used for the DDL trigger The following example is used to disable the trigger on the database. Code: DISABLE TRIGGER trg1 ON DATABASE Enabling a Trigger User can disable the trigger when user wants them to be restricted. If there is a requirement for the trigger, it is enabled again. The syntax for enabling a trigger is as shown below: Code: ENABLE TRIGGER { [ schema_name . ] trigger_name | ALL } ON { object_name | DATABASE } User can enable the trigger using the following statement. Code: ENABLE TRIGGER trg1 ON DATABASE