Triggers

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 _at__at_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 ltgt ' ' and room_ID ltgt 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 _at_counter int
• IF UPDATE(Building_ID) or UPDATE(Room_ID)
  BEGIN
• UPDATE tblClassSection
• SET tblClassSection.Building_IDInserted.Bui
  lding_ID,
• tblClassSection.Room_ID Inserted.Room_ID
• FROM titleauthor, deleted, Inserted
• WHERE tblClassSection.Building_IDDeleted.Bu
  ilding_ID
• and tblClassSection.Room_ID Deleted.Room_ID
• SET _at_counter_at__at_rowcount
• IF (_at_counter gt 0)
• RAISERROR ('d rows of tblClassSection were
  updated as a result of an update to tblLocation
  ', 4, 1, _at_counter)
• END

22
Delete Trigger

• CREATE TRIGGER DEL_Location
• ON tblLocation
• FOR DELETE
• AS
• DECLARE _at_counter int
• UPDATE tblClassSection
• SET tblClassSection.Building_ID' ',
• tblClassSection.Room_ID 0
• FROM tblClassSection, deleted
• WHERE tblClassSection.Building_IDDeleted.Bu
  ilding_ID
• and tblClassSection.Room_ID Deleted.Room_ID
• set _at_counter_at__at_rowcount
• IF (_at_countergt0)
• RAISERROR('d rows of tblClassSection set to
  default as a result of a delete to the
  tblLocation table', 4, 1, _at_counter)