Trigger Examples

We’ve explored the capabilities of triggers, so now it’s appropriate to look at more complete examples. I’ll provide CREATE TABLE statements so that you can set up the example, as well as the trigger itself. Try creating the trigger and then modifying it to display the contents of inserted and deleted tables or to change the way it works.

I’ll provide three examples. The code should be a good starting point for you when you are writing your own triggers. Programming is at least 95 percent creative plagiarism, so feel free to copy liberally. (There’s a rumor that the first trigger programmer ever plagiarized his code, too, but we’re still trying to figure out where he got it.)

Cascaded UPDATE and DELETE

You learned about referential integrity on Day 7. Declarative referential integrity (DRI) constraints allow SQL Server to manage standard referential integrity, in which the server blocks any operation that violates referential integrity.

For example, in the relationship between titles and sales, if you were to delete a title with related rows in sales, a DRI constraint on sales would reject the deletion and return an error. In many circumstances, the standard DRI behavior is exactly what’s called for. However, if you had a very large database of archived sales, you might want to allow the deletion of a row in titles if the book had not sold in, say, 12 months, or if it had gone out of print.

The tricky thing is, you have to delete all the related sales rows or you would end up with a table that violated referential integrity (foreign keys referencing non-existent primary keys). That’s where the trigger comes in. This trigger cascades the delete from the titles table to the sales table.

A cascaded delete is a delete that moves down a chain of dependent tables, from a primary key to a set of foreign keys.

Table Creation Statements

To make this work, we need quick copies of the sales and titles tables:

select     *
into       my_titles
from        pubs..titles
select     *
into      my_sales
from       pubs..sales

Now, don’t blow it and add a DRI constraint between these tables! If you do, the DRI constraint will block the delete in my_titles long before the trigger gets hold of it, so our trigger will never fire.

Trigger Code

The next step is to figure out which table the trigger goes on. That’s easy if you think about the statement that will cause it to fire:

delete       my_titles
where   title_id    =   …

The trigger needs to be on my_titles for delete.

create   trigger      tr_my_titles_d_1
on    my_titles  for   delete
as
if  @@rowcount  =  0  return
if       exists    (
          select   *
                from deleted d join my_sales s on d.title_id =  s.title_id
                where datediff(dd, ord_date, getdate()) < 365)
begin
           raiserror (“Current sales exist: delete failed”, 16, 1)
        rollback tran
      return
end
delete     my_sales
  from    deleted   d
  where      d.title_id   =   my_sales.title_id
if @@error <> 0
begin
       raiserror (“Trigger failed to delete related rows in sales
       table”, 16, 1)
       rollback transaction
    return
end
return

The first step is to make sure that there are rows affected.

if  @@rowcount  =  0  return

If no rows were affected by the DELETE statement, you exit quickly to release any locks that might be in place.

The next step is to make certain that the related rows in the stores table are more than 12 months old—if there’s recent activity, the delete needs to be blocked.

if   exists    (
      select   *
            from  deleted  d  join  my_sales  s  on  d.title_id
            = s.title_id
          where   datediff(dd, ord_date,  getdate())  <  365)
begin
      raiserror  (“Current  sales  exist:  delete failed”,  16,  1)
    rollback   tran
   return
end

The code works well whether one or many rows were deleted from titles. Be certain that triggers run well regardless of the number of rows affected. Don’t forget to raise an error before returning.

The last step is to delete the rows from my_sales.

delete     my_sales
    from   deleted  d
    where     d.title_id   =   my_sales.title_id
if @@error  <>  0
begin
      raiserror  (“Trigger  failed  to  delete  related  rows  in  sales
      table”,  16,  1)
      rollback    transaction
   return
end

The DELETE based on a join works only in T-SQL (it’s not ANSI standard). In this case, the statement deletes all the rows in my_sales with title_id’s found in deleted. After the operation, you should check for an error, raise an error, and roll back if there was one.

---

Note:  
If there’s a weakness in this approach, it’s that SQL Server must join deleted to my_sales twice: once to check the cascade condition, the second time to delete the rows. The alternative is to delete the rows in my_sales in this trigger; then, in a delete trigger on the my_sales table, you can verify the deletion date.

This is called a nested trigger. The server executes a statement from a trigger (such as delete my_sales ), which fires a subsequent trigger. Trigger nesting is limited to 16 levels (the same limit applies to stored procedure nesting). If you exceed the nesting level, SQL Server aborts the batch and rolls back the transaction.

Triggers are not recursive. If you modify table X within a trigger on table X, the trigger will not fire a second time.

---