Trigger

1. “Triggers ”

2. Index:- Implementation of Triggers Example Types of Triggers DML Triggers DDL Triggers Recursive and Nested Triggers Disabling Trigger, Enabling Triggers, Modifying Triggers

3. Triggers Triggers are special types of Stored Procedures that are ran automatically by the database whenever a certain modification (event) occurs. The modification statements may include INSERT, UPDATE, and DELETE . User cannot fire the trigger explicitly , it gets fired implicitly on the basis of certain specified event Not a stand alone object

4. Example:- If you write a letter to your friend and then drop it in a mailbox without putting enough postage on the envelope, you trigger the post office to return the letter to you along with a friendly reminder that you need to add postage. This reminder is triggered by the lack of a stamp; otherwise, the letter would have gone through the mail without any delays. Different post office triggers would be invoked if you provided enough postage but forgot to write your friend’s address

5. Types of Triggers: We can create TWO types of triggers: 1) DML (data manipulation language) triggers DML triggers run when insert, update or delete statements modify data in the specified table or view .  Insert : triggers are invoked by insert statement  Update : triggers are invoked by update statement  Delete : triggers are invoked by delete statement CONT…..

6. Types of Triggers: 2) DDL (data definition language) triggers DDL triggers run when events such as creating, altering or dropping an object occurs on the server Used for database administration tasks such as auditing and controlling object access.

7. Triggers can get fired in two different MODES : 1) For/After Trigger : It gets fired only after the sql server completes all actions successfully on a specified table. Can rollback all the earlier transactions by issuing ROLLBACK For example on inserting a row on a table, the trigger defined on the INSERT operation fires only after the row gets successfully inserted and if the insert fails sql does not execute the trigger. 2) Instead of Trigger: It causes the code present in the trigger to execute instead of the operation that caused the trigger to fire. If we define INSTEAD OF Trigger on the above mentioned table, insert would not happen but the trigger gets fired

8. For / After Trigger: Insert/update/delete into/from table Inserts/ updates/ deletes the value in the table Fires the trigger I nserts the value in the INSERTED /DELETED table as well Can be Rolled back Using ROLLBACK Executes SQL of TRIGGER

9. Instead Of Trigger: Insert /Update/Delete command Table INSTEAD OF Insert/Update/Delete trigger INSTEAD OFINSTEAD OF INSTEAD OFINSTEAD OF Executes the querry in the trigger

10. Syntax of Trigger: CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] {     { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }                AS                sql_statement [... n ]     } } CONT…….. DML Trigger:

11. Arguments: Trigger_name: Is the name of the trigger. Table | view Is the table or view on which the trigger is executed WITH ENCRYPTION Code in the trigger will be encrypted when it is created AFTER Specifies that the trigger is fired only when all operations specified in the triggering SQL statement have executed successfully INSTEAD OF Specifies that the trigger is executed instead of the triggering SQL statement

12. Arguments: { [DELETE] [,] [INSERT] [,] [UPDATE] } Are keywords that specify which data modification statements, when attempted against the table or view, activate the trigger. WITH APPEND Specifies that an additional trigger of an existing type should be added. NOT FOR REPLICATION Indicates that the trigger should not be executed when a replication process modifies the table involved in the trigger

13. Syntax of Trigger: CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH ENCRYPTION ] {     { {FOR | AFTER } { event_type | event_group } [ ,...n ]                AS                 sql_statement [... n ]     } } CONT…….. DDL Trigger:

14. Arguments: DATABASE If specified, the trigger fires whenever event_type or event_group occurs in the current database. ALL SERVER If specified, the trigger fires whenever event_type or event_group occurs anywhere in the current server. event_type Is the name of a Transact-SQL language event that, after execution, causes a DDL trigger to fire. (create, alter, drop) event_group The DDL trigger fires after execution of any Transact-SQL language event that belongs to event_group.

15. Event types….. Create: certificate, assembly, index, function, procedure, role, rule etc…….. Alter: certificate, assembly, index, function, procedure, role, rule etc…….. Drop: certificate, assembly, index, function, procedure, role, rule etc……..

16. Working /Magic Tables : Triggers have access to two special tables called : Inserted and deleted. Insert operation Delete operation Update operation INSERTED DELETED Each row that was inserted in the table Each row that was deleted From the table Has after image of the row updated Has before image of the row updated CONT…….. Deleted table will be Empty Inserted table will be empty

17. Recursive and Nested Triggers: Recursive Triggers : A trigger that cause itself to fire is called recursive trigger. For example update trigger is created on boat table that modifies a column in boat table, the modification in the trigger causes the trigger to fire again leading to an unending chain of transactions. For this in sql server by default the RECURSIVE_TRIGGERS option is set off and you must explicitly turn on this option BOAT table UPDATE Statement UPDATE Trigger Update statement fires the update trigger Updates the column Update boat table

18. Nested Triggers: COMPANY Table EMPLOYEE Table UPDATE Trigger UPDATE Trigger UPDATE Statement

19. Trigger firing order: sp_settriggerorder [@triggername =] '<trigger name>', [@order =] '{FIRST|LAST|NONE}', [@stmttype =] '{INSERT|UPDATE|DELETE}'

20. Common Use of Trigger: Referential Integrity. Data Integrity Rules Creating Audit Trails: Functionality similar to a CHECK constraint, but which works across tables, databases, or even servers. etc……..

21. Actions that c annot be performed using triggers : Database cannot be created, altered, dropped, backed up or restored. Structural changes cannot be made to table that caused trigger to fire. Sql server does not support triggers against system objects. Triggers gets fired only in response to logged operations hence minimally logged operations such as TRUNCATE and WRITETEXT do not cause triggers to fire.

22. Disabling Triggers:- Sometimes triggers can get in the way of what we are trying to accomplish. For example, suppose that we wrote complex triggers on a table to perform a variety of data integrity and validation checks to protect database from incorrect information. Now suppose that we are given a text file containing several million rows of cleaned data that needs to be inserted into this table. These triggers will likely get in the way and cause the data load to take a very long time. Here we can use the DISABLE TRIGGER statement to tell SQL Server 2005 to set one or more triggers: DISABLE TRIGGER Trigger name ON Tablename CONT…..

23. Disabling Triggers:- If you want to disable all triggers for a table DISABLE TRIGGER ALL ON TABLENA ME Disables all DDL triggers for the whole database DISABLE TRIGGER ALL ON DATABASE Disable all the DDL triggers for the entire server DISABLE TRIGGER ALL ON ALL SERVER CONT……

24. Disabling Triggers:- Check whether the trigger is active or not SELECT name, is_disabled FROM sys.triggers WHERE name = ‘TRIGGER_NAME’ A value of ‘ 1’ in disabled column means that the trigger is disabled; a value of ‘ 0’ means that it is active

25. Enabling Triggers:- If you want to disable all triggers for a table ENABLE TRIGGER ALL ON TABLENAME Disables all DDL triggers for the whole database ENABLE TRIGGER ALL ON DATABASE Disable all the DDL triggers for the entire server ENABLE TRIGGER ALL ON ALL SERVER

26. Modifying Triggers:- If you need to make a modification to the trigger’s logic, you can use the ALTER TRIGGER statement. Altering a trigger often means that you must re-type the code for the entire trigger. For this you can retrieve it by simply running the sp_helptext stored procedure: Sp_helptext trigger_name On the other hand, if you want to rename the trigger, use the combination of DROP TRIGGER and CREATE TRIGGER statements

27. Deleting Triggers:- For a DML trigger, the DROP TRIGGER statement looks like this: DROP TRIGGER Trigger_name Getting rid of a DDL trigger for just one database looks like this: DROP TRIGGER Trigger_name ON DATABASE DROP TRIGGER statement to remove a DDL trigger for all databases on your server: DROP TRIGGER Trigger_name ON ALL SERVER

28. References : www.theparticle.com/cs/bc/dbms/triggers.pdf www.sql-server-performance.com/articles/dev/triggers_2000_p6.aspx Microsoft SQL Server 2005 Implementation And Maintenance . SQL Server 2005 Bible.

29. Thanks…….. Questions ….!!!! NO Please