Chapter 5 Building Triggers and Procedures

Multiple triggers

Some DBMSs allow you to have multiple triggers of the same type (time and event) defined for any given table. Triggers of the same type are triggers that are invoked for the same insert, update, or delete event.

Example

A company is considering large numbers of candidates for new positions in various posts. You want to ensure that all new employees will have a salary that is within the range of others working in the same field, and less than his or her prospective manager.

On an EMPLOYEE table, you create two BeforeInsert triggers , tibTestSalry1_ EMPLOYEE to verify that a proposed salary falls within the correct range, and tibTestSalry2_ EMPLOYEE to verify that the proposed salary is less than that of the prospective manager.

create trigger tibTestSalry1 before insert order 1 on EMPLOYEE
referencing new as new_ins for each row
begin
    
   [Trigger code]
   
end

create trigger tibTestSalry2 before insert order 2 on EMPLOYEE
begin
    
   [Trigger code]
   
end

For a specified table, you can indicate the order that a trigger executes, or fires, within a group of triggers of the same type.

To indicate trigger order:

1. Click the Definition tab from the trigger property sheet.
2. Select a number from the Order dropdown list box to indicates the position in the firing order that the trigger fires.
3. Click OK in each of the dialog boxes.

Copyright (C) 2005. Sybase Inc. All rights reserved.