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 • 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
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
Example 1 • Create a table that looks like this: create table test_trigger (col1 int, col2 char(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')
Example 1 (cont) • Define the Trigger create trigger delete_test on test_trigger for delete as print 'You just deleted a row!'
Example 1 (cont) • Issue the following command delete test_trigger where col1 = 0 • What happened? Why?
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!'
Example 1 (cont) • Try again delete test_trigger where col1 = 0 Now what happened?
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
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
Trigger Example • The following code is example of a Trigger enforcing RI • It is between tblLocation and tblClassSection table in our StudentClass database
What are we doing • ON DELETE SET DEFAULT • ON UPDATE CASCADE • ON INSERT NO ACTION
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
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
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
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
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
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
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
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)