1 / 22

Triggers

Triggers. Triggers. Are event-driven actions Are written in Transact-SQL Are a special kind of Stored Procedure Are tied to a specific table. Why Triggers. To maintain data integrity rules that extend beyond simple referential integrity To keep running totals

zasha
Download Presentation

Triggers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Triggers

  2. Triggers • Are event-driven actions • Are written in Transact-SQL • Are a special kind of Stored Procedure • Are tied to a specific table

  3. Why Triggers • To maintain data integrity rules that extend beyond simple referential integrity • To keep running totals • To keep computed columns updated • To implement a referential action, such as cascading deletes • To maintain an audit record of changes • To invoke an external action because of data changes

  4. When Do They Fire • A trigger is a stored procedure that is tied to a specific action on a table • These actions traditionally include • Insert • Update • Delete • The Trigger ‘fires’ based on a command beign executed that is tied to the trigger

  5. Example 1 • Create a table that looks like this: create table test_trigger (col1 int, col2 char(6))

  6. Example 1 (cont) • Issue the following inserts insert into test_trigger values (1, 'First') insert into test_trigger values (1, 'Second') insert into test_trigger values (1, 'Third') insert into test_trigger values (1, 'Fourth') insert into test_trigger values (1, 'Fifth')

  7. Example 1 (cont) • Define the Trigger create trigger delete_test on test_trigger for delete as print 'You just deleted a row!'

  8. Example 1 (cont) • Issue the following command delete test_trigger where col1 = 0 • What happened? Why?

  9. Example 1 (cont) • Correct the trigger create tigger delete_test on test_trigger for delete as if @@rowcount = 0 return print 'You just deleted a row!'

  10. Example 1 (cont) • Try again delete test_trigger where col1 = 0 Now what happened?

  11. RI Revisited • No Action • Disallows any action if it violate the RI rule • The only action implemented in SQL Server • Set Null • Updates the reference table to a NULL • Set Default • Updates the reference table to a Default • Cascade • Updates the referencing table so the FK values are the same as the primary key of the referenced table

  12. Triggers and Declarative RI • You can enforce RI rules by defining Foreign Keys or by defining Triggers • You cannot use both of them together on the same table

  13. Trigger Example • The following code is example of a Trigger enforcing RI • It is between tblLocation and tblClassSection table in our StudentClass database

  14. What are we doing • ON DELETE SET DEFAULT • ON UPDATE CASCADE • ON INSERT NO ACTION

  15. ON DELETE SET DEFAULT • If we try to delete a record from tblLocation • Allow the delete • Update and tblClassSection records pointing to that location to the default values of: • BUILDING_ID = ‘NA’ • ROOM_ID = 0

  16. ON UPDATE CASCADE • If the BUILDING_ID and/or ROOM_ID are changed in tblLocation • Change the BUILDING_ID and/or ROOM_ID in tblClassSection • Remember that the BUILDING_ID and ROOM_ID in tblClassSection as the original values in tblLocation

  17. ON INSERT NO ACTION • When inserting a row in tblClassSeciton the Location_ID/Room_ID combination must exist in tblLocation • This also applies when updating a row in tblClassSection to a new location

  18. Insert Trigger CREATE TRIGGER INS_UPD_ClassSection on tblClassSection FOR INSERT, UPDATE AS -- Do any rows exist in the inserted table that do not have a matching -- location in tblLocation. If not produce an error IF EXISTS (select * from inserted isrt where building_ID <> ' ' and room_ID <> 0 and not exists (select 1 from tblLocation loc where isrt.building_ID = loc.building_ID and isrt.room_ID = loc.room_ID)) BEGIN RAISERROR('No matching location found. Statement will be aborted.', 16, 1) ROLLBACK TRAN END

  19. RAISERROR • RAISERROR is like the PRINT, only different • It prints a message and also returns an severity code and state • Note the Trigger returned a 16, 1 • These values are usually set to shop standards

  20. Inserted and deleted • These are virtual tables that hold the values in the table before the insert (or delete) • This makes it possible to know what the table looked like before the triggering statement was executed

  21. Update Trigger CREATE TRIGGER UPD_Location ON tblLocation FOR UPDATE AS DECLARE @counter int IF UPDATE(Building_ID) or UPDATE(Room_ID) BEGIN UPDATE tblClassSection SET tblClassSection.Building_ID=Inserted.Building_ID, tblClassSection.Room_ID = Inserted.Room_ID FROM titleauthor, deleted, Inserted WHERE tblClassSection.Building_ID=Deleted.Building_ID and tblClassSection.Room_ID = Deleted.Room_ID SET @counter=@@rowcount IF (@counter > 0) RAISERROR ('%d rows of tblClassSection were updated as a result of an update to tblLocation ', 4, 1, @counter) END

  22. Delete Trigger CREATE TRIGGER DEL_Location ON tblLocation FOR DELETE AS DECLARE @counter int UPDATE tblClassSection SET tblClassSection.Building_ID=' ', tblClassSection.Room_ID = 0 FROM tblClassSection, deleted WHERE tblClassSection.Building_ID=Deleted.Building_ID and tblClassSection.Room_ID = Deleted.Room_ID set @counter=@@rowcount IF (@counter>0) RAISERROR('%d rows of tblClassSection set to default as a result of a delete to the tblLocation table', 4, 1, @counter)

More Related