Cascading Triggers Teradata

A cascading trigger results when a triggering statement fires a trigger, which in turn fires another trigger. The situation is such that the table being updated by the first trigger also has a trigger associated with it and becomes the new subject table. Then the second trigger updates a table that has a trigger associated with it. So, each triggered statement in turn becomes a triggering statement.

The only thing a trigger cannot do is to change the subject table on which the trigger is defined. However, a subsequent trigger may come back and update the original subject table. Caution should be exercised here so that the triggers do not cascade indefinitely. This constitutes an infinite loop and will cascade until they run out of either Permanent or Transient Journal space, or the transaction is aborted.

Cascading Triggers

Cascading Trigger example:

In this cascade example, there are three tables: CasTbl_1, CasTbl_2 and CasTbl_3. Each table is defined with two columns called: Col1 and Col2. At the beginning of the process, all three tables are empty.

All three tables use basically the same CREATE TABLE to build the initial definitions; only the names have been changed.

Now the triggers are defined to monitor the rows in the tables called CasTlb_1 and CasTbl_2:

Now that the tables and triggers have been defined, the trigger statement can be issued:

INSERT INTO CasTbl_1 values (1, 4);

The next SELECT operations are to verify that the triggers worked:

SEL * FROM CasTbl_1; 1 Row Returnedt SELECT operations are to verify that the triggers worked

The above output is from the original insert into the first table.

Look what happens when a SELECT is performed on each of the other two tables:

SEL * FROM CasTbl_2; 1 Row Returnedoutput is from the original insert into the first table SEL * FROM CasTbl_3: 1 Row Returned output is from the original insert into the first table

The first trigger inserted a row into CasTbl_2 as a result of the original insert into CasTbl_1. Then, the second trigger inserted a row into CasTbl_3 because of the inserted row into CasTbl_2. All of this happened as a result of the original INSERT; they cascaded from the original row.

Remember the one thing to avoid when using cascading triggers. Do not create a trigger on either CasTbl_2 or CasTbl_3 that will insert a row into CasTbl_1. This causes an indefinite loop of the INSERT operations that will continue until aborted.



Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

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

Teradata Topics