Sequencing Triggers Teradata

All of the previously discussed triggers were independent from each other because the are on different tables and have a different ACTION. Therefore, the sequence in which they are fired does not matter. However, sometimes the sequence does matter. When this is the case, another mechanism must be used to guarantee that the triggers are fired in the proper sequence to insure that all modifications are made in the correct ORDER.

The ORDER option allows for the definition of a sequence number to be associated with each trigger. Valid values are 1 through 32,767. All triggers with an ORDER of 1, fire before the first ORDER 2 trigger. All the ORDER 2 triggers fire before the first 3 and so on until there are no more triggers associated with the initial triggering statement.

This ORDER process continues until there are no more triggers, or until a triggered action fails. Remember, that triggers are always part of a transaction and if part of a transaction fails, it stops and every operation to that point must ROLLBACK. When ORDER is not specified, it defaults to a value of 32,767. Triggers with the same ORDER value fire randomly until the last one with that same value finishes.

As an example, instead of using the cascading triggers seen previously, it might be desirable to have both triggers defined on CasTbl_1. That way, no INSERT need be performed on CasTbl_2, only on CasTbl_1, as seen with the two triggers created below:

Since both of the above triggers are AFTER INSERT, they both have the same trigger action. In this example, it was decided that INSTrig1 should fire before INSTrig2 because its value in the ORDER is less. The result will be identical if the ORDER is not specified for INSTrig2 because the value of 100 is less than 32,767. Using the ORDER allows this level of control, but it is optional and only needed to control sequencing. Without the ORDER, they are both 32,767 and fire randomly.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics