Triggers

1. Triggers How triggers work Creating triggers Using triggers to maintain referential integrity Multirow considerations Nesting triggers Rules associated with triggers Disabling triggers Dropping triggers Getting information about triggers

2. How Triggers Work A trigger is a stored procedure that goes into effect when you insert,delete, or update data in a table. You can use triggers to perform a numberof automatic actions, such as cascading changes enforcing column restrictions comparing the results of datamodifications maintaining the referential integrity of data across adatabase.

3. How Triggers Work A trigger isspecific to one or more of the data modification operations, update, insert,and delete and is executed once for each SQL statement.

4. How Triggers Work For example, to prevent users from removing any publishing companiesfrom the publishers table, you could use this trigger: createtriggerdel_pub on publishers for delete as begin rollback transaction print ‘You cannot delete any publishers!’ end

5. How Triggers Work A trigger fires only after the data modification statement has completed andServer has checked for any datatype, rule, or integrity constraintviolation. The trigger and the statement that fires it are treated as a singletransaction that can be rolled back from within the trigger. If Serverdetects a severe error, the entire transaction is rolled back.

6. How Triggers Work Triggers are most useful in these situations: Triggers can cascade changes through related tables in the database. Triggers can disallow, or roll back, changes that would violate referentialintegrity, canceling the attempted data modification transaction. Triggers can enforce restrictions that are much more complex than thosethat are defined with rules. Triggers can perform simple “what if” analyses. For example, a trigger cancompare the state of a table before and after a data modification and takeaction based on that comparison.

7. Creating Triggers Here is the complete create trigger syntax: create trigger [owner.]trigger_name on [owner.]table_name {for / instead of{insert , update , delete} as SQL_statements Or, using the if update clause: createtrigger [owner.]trigger_name on [owner.]table_name for {insert , update} as [ifupdate (column_name ) [{and | or} update (column_name )]...] SQL_statements [ifupdate (column_name ) [{and | or} update (column_name )]... SQL_statements ]...

8. Creating Triggers SQL statements that are not allowed in triggers: Since triggers execute as part of a transaction, the following statements are not allowed in a trigger: • All create commands, including create database, create table, create index, create procedure, create default, create rule, create trigger, and create view • All drop commands • alter table and alter database • truncate table • grant and revoke • update statistics • reconfigure • load database and load transaction • disk init, disk mirror, disk refit, disk reinit, disk remirror, disk unmirror • select into

9. Trigger Test Tables Referential integrity triggers keep the values of foreign keys in line with thosein primary keys. When a data modification affects a key column, triggerscompare the new column values to related keys by using temporary work tablescalled trigger test tables. When you write your triggers, you base yourcomparisons on the data that is temporarily stored in the trigger test tables.

10. Trigger Test Tables Server uses two special tables in trigger statements: the deleted tableand the inserted table. These aretemporary tables used in trigger tests. The deleted table stores copies of the affected rows during delete andupdate statements. During the execution of a delete or update statement,rows are removed from the trigger table and transferred to the deletedtable. The deleted and trigger tables ordinarily have no rows in common.

11. Trigger Test Tables The inserted table stores copies of the affected rows during insert andupdate statements. During an insert or an update, new rows are added tothe inserted and trigger tables at the same time. The rows in inserted arecopies of the new rows in the trigger table. An update is a delete followed by an insert; the old rows are copiedto the deleted table first; then the new rows are copied to the trigger table andto the inserted table.

12. InsertTriggerExample When you insert a new foreign key row, make sure the foreign key matches aprimary key. The trigger should check for joins between the inserted rows(using the inserted table) and the rows in the primary key table, and then rollback any inserts of foreign keys that do not match a key in the primary keytable. The following trigger compares the title_id values from the inserted table withthose from the titles table. It assumes that you are making an entry for theforeign key and that you are not inserting a null value. If the join fails, thetransaction is rolled back.

13. PUBS2 DB Schema

14. InsertTriggerExample create trigger forinsertrig1 on salesdetail for insert as if (select count(*) from titles, inserted where titles.title_id = inserted.title_id) != @@rowcount /* Cancel the insert and print a message.*/ begin rollback transaction print ‘No, the title_id does not exist intitles.’ end /* Otherwise, allow it. */ else print ‘Added! All title_id’s exist in titles.’

15. InsertTriggerExample @@rowcount (Returns the number of rows affected by the last statement)refers to the number of rows added to the salesdetail table. Thisis also the number of rows added to the inserted table. The trigger joins titlesand inserted to determine whether all the title_ids added to salesdetail exist inthe titles table. If the number of joined rows, which is determined by the selectcount(*) query, differs from @@rowcount, then one or more of the inserts isincorrect, and the transaction is canceled.

16. InsertTriggerExample This trigger prints one message if the insert is rolled back and another if it isaccepted. To test for the first condition, try this insert statement: insert salesdetail values ("7066", "234517", "TC9999", 70, 45) To test for the second condition, enter: insert salesdetail values ("7896", "234518", "TC3218", 75, 80)

17. DeleteTriggerExample When you delete a primary key row, delete corresponding foreign key rows independent tables. This preserves referential integrity by ensuring that detailrows are removed when their master row is deleted. If you do not delete thecorresponding rows in the dependent tables, you may end up with a databasewith detail rows that cannot be retrieved or identified. To properly delete thedependent foreign key rows, use a trigger that performs a cascading delete.

18. CascadingDeleteExample When a delete statement on titles is executed, one or more rows leave the titlestable and are added to deleted. A trigger can check the dependent tables—titleauthor, salesdetail, and roysched—to see if they have any rows with a title_idthat matches the title_ids removed from titles and is now stored in the deletedtable. If the trigger finds any such rows, it removes them.

19. CascadingDeleteExample create trigger delcascadetrig on titles for delete as delete titleauthor from titleauthor, deleted where titleauthor.title_id = deleted.title_id /* Remove titleauthor rows that match deleted(titles) rows.*/ deletesalesdetail fromsalesdetail, deleted wheresalesdetail.title_id = deleted.title_id /* Removesalesdetailrowsthatmatchdeleted ** (titles) rows.*/ deleteroysched fromroysched, deleted whereroysched.title_id = deleted.title_id /* Removeroyschedrowsthatmatchdeleted ** (titles) rows.*/

20. RestrictedDeleteExamples In practice, you may want to keep some of the detail rows, either for historicalpurposes (to check how many sales were made on discontinued titles whilethey were active) or because transactions on the detail rows are not yetcomplete. A well-written trigger should take these factors into consideration.

21. RestrictedDeleteExamples Preventing primary key deletions The deltitle trigger prevents the deletion of a primary keyif there are any detail rows for that key in the salesdetail table. This triggerpreserves the ability to retrieve rows from salesdetail: create trigger deltitle on titles for delete as if (select count(*) from deleted, salesdetail where salesdetail.title_id=deleted.title_id) > 0 begin rollback transaction print ‘You cannot delete a title with sales.’ end In this trigger, the row or rows deleted from titles are tested by being joined with the salesdetail table. If a join is found, the transaction is canceled.

22. RestrictedDeleteExamples Recording errors that occur The following restricted delete prevents deletes if the primary table,titles, has dependent children in titleauthor. Instead of counting the rows fromdeleted and titleauthor, it checks to see if title_id was deleted. This method ismore efficient for performance reasons because it checks for the existence of aparticular row rather than going through the entire table and counting all therows: create trigger restrict_dtrigon titles for delete as if exists (select * from titleauthor, deleted wheretitleauthor.title_id= deleted.title_id) begin rollback transaction raiserror35003 return end To test this trigger, try this delete statement: delete titles where title_id = ‘PS2091’

23. Update TriggerExamples The following example cascades an update from the primary table titles to thedependent tables titleauthor and roysched. create trigger cascade_utrig on titles for update as if update(title_id) begin update titleauthor set title_id = inserted.title_idfromtitleauthor, deleted, inserted wheredeleted.title_id = titleauthor.title_id updateroysched set title_id = inserted.title_idfromroysched, deleted, inserted wheredeleted.title_id = roysched.title_id updatesalesdetail set title_id = inserted.title_idfromsalesdetail, deleted, inserted wheredeleted.title_id = salesdetail.title_id end

24. Update TriggerExamples To test this trigger, suppose that the book Secrets of Silicon Valley wasreclassified to a psychology book from popular_comp. The following queryupdates the title_id PC8888 to PS8888 in titleauthor, roysched, and titles. update titles set title_id = ‘PS8888’ where title_id = ‘PC8888’

25. RestrictedUpdate Triggers Restricted update trigger using date functions: The following trigger prevents updates to titles.title_id on the weekend. The ifupdate clause in stopupdatetrig allows you to focus on a particular column,titles.title_id. Modifications to the data in that column cause the trigger togointo action. Changes to the data in other columns do not. When this triggerdetects an update that violates the trigger conditions, it cancels the update andprints a message. If you would like to test this one, substitute the current dayof the week for “Saturday” or “Sunday”.

26. Restricted Update Triggers Restricted update trigger using date functions: create trigger stopupdatetrig on titles for update As /* If an attempt is made to change titles.title_id ** on Saturday or Sunday, cancel the update. */ if update (title_id) and datename(dw, getdate())in ("Saturday", "Sunday") begin rollback transaction print ‘We do not allow changes to ‘ print ‘primary keys on the weekend.’ end

27. Restricted Update Triggers Restricted update triggers with multiple actions: You can specify multiple trigger actions on more than one column using ifupdate. The following example modifies stopupdatetrig to include additionaltrigger actions for updates to titles.price or titles.advance. In addition topreventing updates to the primary key on weekends, it prevents updates to theprice or advance of a title, unless the total revenue amount for that titlesurpasses its advance amount. You can use the same trigger name because themodified trigger replaces the old trigger when you create it again.

28. Restricted Update Triggers Restricted update triggers with multiple actions: create trigger stopupdatetrig on titles for update as if update (title_id)and datename(dw, getdate())in (‘Saturday’, ‘Sunday’) begin rollback transaction Print’We do not allow changes to’ print ‘primary keys on the weekend!’ end if update (price) or update (advance) if exists (select * from inserted where (inserted.price * inserted.total_sales)< inserted.advance) begin rollback transaction print ‘We do not allow changes to price or’ print ‘advance for a title until its total’ print ‘revenue exceeds its latest advance’ End

29. MultirowConsiderations Multirow considerations are particularly important when the function of atrigger is to recalculate summary values.Triggersareused to maintain summary values should contain group by clauses orsubqueriesthat perform implicit grouping. This creates summary values whenmore than one row is being inserted, updated, or deleted. Since a group byclause imposes extra overhead, the following examples are written to testwhether @@rowcount = 1, meaning that only one row in the trigger table wasaffected. If @@rowcount = 1, the trigger actions take effect without a group byclause.

30. Inserttrigger example using multiple rows The following insert trigger updates the total_sales column in the titles tableevery time a new salesdetail row is added. It goes into effect whenever yourecord a sale by adding a row to the salesdetail table. It updates the total_salescolumn in the titles table so that total_sales is equal to its previous value plusthe value added to salesdetail.qty. This keeps the totals up to date for inserts intosalesdetail.qty.

31. Inserttrigger example using multiple rows /* when @@rowcount is greater than 1, use a group by clause */ update titles set total_sales = total_sales + (select sum(qty) from inserted group by inserted.title_id havingtitles.title_id = inserted.title_id) create trigger intrig on salesdetail for insert as /* check value of @@rowcount */ if @@rowcount = 1 update titles set total_sales = total_sales+ qty from inserted where titles.title_id = inserted.title_id else

32. Delete trigger example using multiple rows Else/* when rowcount is greater than 1, use a group by clause */ update titles set total_sales =total_sales - (select sum(qty) from deleted group by deleted.title_id having titles.title_id = deleted.title_id) This trigger goes into effect whenever a row is deleted from the salesdetailtable. It updates the total_sales column in the titles table so that total_sales isequal to its previous value minus the value subtracted from salesdetail.qty. The next example is a delete trigger that updates the total_sales column in thetitles table every time one or more salesdetail rows are deleted. create trigger deltrig on salesdetail for delete as /* check value of @@rowcount */ if @@rowcount = 1 update titles set total_sales = total_sales - qty from deleted where titles.title_id = deleted.title_id .

33. Update trigger example using multiple rows The following update trigger updates the total_sales column in the titles tableevery time the qty field in a salesdetail row is updated. Recall that an update isan insert followed by a delete. This trigger references both the inserted and thedeleted trigger test tables.

34. Update trigger example using multiple rows /* when rowcount is greater than 1, use a group by clause */ begin update titles set total_sales = total_sales + (select sum(qty) from inserted group by inserted.title_id having titles.title_id = inserted.title_id) update titles set total_sales = total_sales - (select sum(qty) from deleted group by deleted.title_id having titles.title_id = deleted.title_id) end end create trigger updtrig on salesdetail for update as if update (qty) begin /* check value of @@rowcount */ if @@rowcount = 1 update titles set total_sales = total_sales + inserted.qty- deleted.qty from inserted, deleted where titles.title_id = inserted.title_id and inserted.title_id = deleted.title_id else

35. Nesting triggers • Triggers can nest to a depth of 16 levels. The current nesting level is stored inthe @@nestlevel global variable. Nesting is enabled at installation. A SystemAdministrator can turn trigger nesting on and off with the allow nested triggersconfiguration parameter. • If nested triggers are enabled, a trigger that changes a table on which there isanother trigger fires the second trigger, which can in turn fire a third trigger,and so forth. If any trigger in the chain sets off an infinite loop, the nesting levelis exceeded and the trigger aborts. You can use nested triggers to performuseful housekeeping functions such as storing a backup copy of rows affectedby a previous trigger.

36. Nesting triggers • For example, you can create a trigger on titleauthor that saves a backup copy oftitleauthorrows that was deleted by the delcascadetrig trigger. With thedelcascadetrigtrigger in effect, deleting the title_id “PS2091” from titles alsodeletes the corresponding row(s) from titleauthor. To save the data, you cancreate a delete trigger on titleauthor that saves the deleted data in another table,del_save: create trigger savedel on titleauthor for delete as insert del_save select * from deleted

37. Using INSTEAD OF Triggers • The idea behind an INSTEAD OF trigger is that it fires instead of the INSERT, UPDATE, or DELETE statement that triggers it. Plus, you can define these triggers on views. • In other words, views that would normally not be can have their underlying tables updated in anupdatable INSTEAD OF trigger. • An INSTEAD OF trigger can support inserts, updates, and deletes that reference data in more than one table, and can also allow you to code more complex logic that will be applied whenever the view or table is modified. • You could present a friendly interface through a view and behind the scenes any updates to that simple view could be propagated to many different underlying tables.

38. Using INSTEAD OF Triggers • For example, a view containing a join between the products and the categories tables would normally only allow you to update either one or the other in a single UPDATE statement, not both: CREATE VIEW vwProductByCategoryItrig AS SELECT C.Category, P.ProductID, P.Product FROM tblProduct AS P INNER JOIN tblCategory AS C ON P.CategoryID = C.CategoryID Tryexecutingthisupdateandyou’llget an error: UPDATE vwProductByCategoryItrig SET Product = 'SharkThingys', Category = 'Thingys' WHERE ProductID = 1

39. Using INSTEAD OF Triggers The following INSTEAD OF trigger independently updates each table anytime an attempt is made to update the view. The trigger takes over and performs two separate updates instead of letting things take their course and attempting a single illegal update: CREATE TRIGGER trigUpdateBoth ON vwProductByCategoryItrig INSTEAD OF UPDATE AS SET NOCOUNT ON UPDATE tblProduct SET tblProduct.Product = (SELECT inserted.Product FROM inserted) WHERE tblProduct.ProductID = (SELECT inserted.ProductID FROM inserted) UPDATE tblCategory SET tblCategory.Category = (SELECT inserted.Category FROM inserted) WHERE tblCategory.Category = (SELECT deleted.Category FROM deleted)

40. Using INSTEAD OF Triggers Although a single UPDATE statement that tried to update both the Product and the Category tables through a view would otherwise fail, the INSTEAD OF trigger fires instead of the normal UPDATE statement and explicitly writes changes back to both tables. The user or client application doesn’t even have to know what the underlying tables are or how they are related. The update statement will now succeed. UPDATE vwProductByCategoryItrig SET Product = 'Shark Thingys', Category = 'Thingys' WHERE ProductID = 1

41. Rules associated with triggers Triggersandpermissions: A trigger is defined on a particular table. Only the owner of the table has createtrigger and drop trigger permissions for the table. These permissions cannot betransferred to others. For example, Jose owns salesdetail and creates a trigger on it. The trigger issupposed to update titles.total_sales when salesdetail.qty is updated. However,Mary is the owner of titles, and has not granted Jose permission on titles. WhenJose tries to update salesdetail, Server detects the trigger and Jose’slack of permissions on titles, and rolls back the update transaction. Jose musteither get update permission on titles.total_sales from Mary or drop the triggeron salesdetail.

42. Disabling triggers The insert, update, and delete commands normally fire any trigger theyencounter, which increases the time needed to perform the operation.To disabletriggers during bulk insert, update, or delete operations, you can use the disabletrigger option of the alter table command. You can use the disable trigger optioneither to disable all the triggers associated with the table, or to specify aparticular trigger to disable. However, any triggers you disable will not be firedafter the copy is complete.Forexample: alter table [database_name.[owner_name].]table_name {enable | disable } trigger [trigger_name] Disable altertable pubs2 disabledel_pubs Enable: altertable pubs2 enabledel_pubs

43. Dropping triggers You can remove a trigger by dropping it or by dropping the trigger table withwhich it is associated. The drop trigger syntax is: drop trigger [owner.]trigger_name[, [owner.]trigger_name]... When you drop a table, Server drops any triggers associated with it.drop trigger permission defaults to the trigger table owner and is nottransferable.

44. Getting information about triggers As database objects, triggers are listed in sysobjects by name. The type columnof sysobjects identifies triggers with the abbreviation “TR”. This query findsthe triggers that exist in a database: select * from sysobjects where type = "TR" The source text for each trigger is stored in syscomments. Execution plans fortriggers are stored in sysprocedures. The system procedures described in thefollowing sections provide information from the system tables about triggers.

45. Getting information about triggers sp_help: You can get a report on a trigger using sp_help. For example, you can getinformation on deltitle as follows: sp_helpdeltitle sp_helptext: To display the source text of a trigger, execute sp_helptext, as follows: sp_helptextdeltitle sp_depends: lists the triggers that reference an object or all the tables or viewsthat the trigger affects. This example shows how to use sp_depends to get a listof all the objects referenced by the trigger deltitle: sp_dependsdeltitle