Home

	Expand Search

Transact-SQL User's Guide

Chapter 16 Triggers: Enforcing Referential Integrity

Using triggers to maintain referential integrity

Update trigger examples

Update trigger examples

The following example cascades an update from the primary table titles to the dependent 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_id
           from titleauthor, deleted, inserted
           where deleted.title_id = titleauthor.title_id
     update roysched
           set title_id = inserted.title_id
           from roysched, deleted, inserted
           where deleted.title_id = roysched.title_id
     update salesdetail
           set title_id = inserted.title_id
           from salesdetail, deleted, inserted
           where deleted.title_id = salesdetail.title_id
end

To test this trigger, suppose that the book Secrets of Silicon Valley was reclassified to a psychology book from popular_comp. The following query updates the title_id PC8888 to PS8888 in titleauthor, roysched, and titles.

update titles
set title_id = "PS8888"
where title_id = "PC8888"

A primary key is the unique identifier for its row and for foreign key rows in other tables. Generally, you should not allow updates to primary keys. An attempt to update a primary key should be taken very seriously. In this case, you need to protect referential integrity by rolling back the update unless specified conditions are met.

Sybase suggests that you prohibit any editing changes to a primary key, for example by revoking all permissions on that column. However, if you want to prohibit updates only under certain circumstances, use a trigger.

Restricted update trigger using date functions

The following trigger prevents updates to titles.title_id on the weekend. The if update clause in stopupdatetrig allows you to focus on a particular column, titles.title_id. Modifications to the data in that column cause the trigger to go into action. Changes to the data in other columns do not. When this trigger detects an update that violates the trigger conditions, it cancels the update and prints a message. If you would like to test this one, substitute the current day of the week for "Saturday" or "Sunday".

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

Restricted update triggers with multiple actions

You can specify multiple trigger actions on more than one column using if update. The following example modifies stopupdatetrig to include additional trigger actions for updates to titles.price or titles.advance. In addition to preventing updates to the primary key on weekends, it prevents updates to the price or advance of a title, unless the total revenue amount for that title surpasses its advance amount. You can use the same trigger name because the modified trigger replaces the old trigger when you create it again.

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

The next example, created on titles, prevents update if any of the following conditions is true:

• The user tries to change a value in the primary key title_id in titles

• The dependent key pub_id is not found in publishers

• The target column does not exist or is null

Before you run this example make sure the following error messages exist in sysusermessages:

sp_addmessage 35004, "titles_utrg - Update Failed: update of primary keys %1! is not allowed."

sp_addmessage 35005, "titles_utrg - Update Failed: %1! not found in authors."

The trigger is as follows:

create trigger title_utrg
on titles
for update as
begin
     declare @num_updated int,
             @col1_var varchar(20),
             @col2_var varchar(20)
/* Determine how many rows were updated. */
select @num_updated = @@rowcount
    if @num_updated = 0
    return
/* Ensure that title_id in titles is not changed. */
if update(title_id)
    begin
       rollback transaction
     select @col1_var = title_id from inserted
     raiserror 35004 , @col1_var
     return
   end
 /* Make sure dependencies to the publishers table are accounted for. */
 if update(pub_id)
   begin
     if (select count(*) from inserted, publishers
         where inserted.pub_id = publishers.pub_id
         and inserted.pub_id is not null) != @num_updated
     begin
         rollback transaction
         select @col1_var = pub_id from inserted
         raiserror 35005, @col1_var
         return
     end
   end
/* If the column is null, raise error 24004 and rollback the
** trigger. If the column is not null, update the roysched table
** restricting the update. */
   if update(price)
     begin
         if exists (select count(*) from inserted 
         where price = null)
     begin
         rollback trigger with
         raiserror 24004 "Update failed : Price cannot be null. "
     end
     else
     begin
         update roysched 
         set lorange = 0,
         hirange = price * 1000
         from inserted
         where roysched.title_id =  inserted.title_id
     end
    end 
end

To test for the first error message, 35004, enter:

update titles
set title_id = "BU7777"
where title_id = "BU2075"

To test for the second error message, 35005:

update titles
set pub_id = "7777"
where pub_id = "0877"

To test for the third error, which generates message 24004:

update titles
set price = 10.00
where title_id = "PC8888"

This query fails because the price column in titles is null. If it were not null, it would have updated the price for title PC8888 and performed the necessary recalculations for the roysched table. Error 24004 is not in sysusermessages but it is valid in this case. It demonstrates the "rollback trigger with raiserror" section of the code.

A change or an update to a foreign key by itself is probably an error. A foreign key is just a copy of the primary key. Never design the two to be independent. If you want to allow updates of a foreign key, you should protect integrity by creating a trigger that checks updates against the master table and rolls them back if they do not match the primary key.

In the following example, the trigger tests for two possible sources of failure: either the title_id is not in the salesdetail table or it is not in the titles table.

This example uses nested if...else statements. The first if statement is true when the value in the where clause of the update statement does not match a value in salesdetail, that is, the inserted table will not contain any rows, and the select returns a null value. If this test is passed, the next if statement ascertains whether the new row or rows in the inserted table join with any title_id in the titles table. If any row does not join, the transaction is rolled back, and an error message is printed. If the join succeeds, a different message is printed.

create trigger forupdatetrig 
on salesdetail 
for update 
as 
declare @row int 
/* Save value of rowcount. */
select @row = @@rowcount  
if update (title_id) 
  begin 
    if (select distinct inserted.title_id 
        from inserted) is null 
      begin 
        rollback transaction 
        print "No, the old title_id must be in"
        print "salesdetail." 
      end 
    else 
      if (select count(*) 
          from titles, inserted 
          where titles.title_id = 
          inserted.title_id) != @row 
        begin 
          rollback transaction 
          print "No, the new title_id is not in"
          print "titles." 
        end 
      else
        print "salesdetail table updated" 
  end