Recursive Triggers

A recursive trigger contains a triggered action which causes the triggering action to reoccur. For example, assume a row trigger is defined with a triggering action that inserts a row into a table. If the triggered action also inserts into the same table, it will cause the trigger to fire again. This will continue until one of two events occur:

• The WHEN clause logic in the trigger definition causes the cycle to end. 

• Sixteen levels of recursion occur. 

The system automatically stops the recursion after sixteen levels to prevent an infinite loop.

Example 1:
CREATE TABLE table1_recur
(col_a INT);
CREATE TRIGGER recur_1
AFTER INSERT ON table1_recur
REFERENCING NEW AS t1
FOR EACH ROW
WHEN (t1.col_a < 10)
INSERT INTO table1_recur (t1.col_a + 1);
INSERT INTO table1_recur( 7 );

SELECT * FROM table1_recur;
      col_a
-----------
          7
          6
          8
         10

DELETE FROM table1_recur;

Now, we will remove the WHEN clause which controls the stopping of the recursion. In theory, this could produce an infinite loop, however the Teradata trigger implementation forces the recursion to rollback with a failure after 16 iterations. 

Example 2:
REPLACE TRIGGER recur_1
AFTER INSERT ON table1_recur
REFERENCING NEW AS t1
FOR EACH ROW
INSERT INTO table1_recur (t1.col_a + 1);
INSERT INTO table1_recur( 7 );
*** Failure 5848 Statement# 1, Info =0 

SELECT * FROM table1_recur;
*** Query completed. No rows found. 

Note that no rows are found in the table because the failure of the transaction has caused all activity to be rolled back. 

Tweet