CREATE TRIGGER Syntax Teradata

Row Trigger

The first syntax shown below is a row trigger. It can "fire" multiple times for each triggering statement, once for every row changed in the table. When using the ROW type of trigger, the REFERENCING statement provides names for the before image of a row via the key phrase OLD AS and also the after image of a row via using NEW AS. Prior to Teradata V2R4, a row trigger could perform any valid DML statement except a SELECT. Since V2R4, it is a more ANSI standard implementation and can only perform an INSERT.

The basic format and syntax to create a row trigger:

Additional discussion and examples are presented to enhance understanding of trigger use after a discussion of a Statement trigger.

Statement Trigger

The second syntax below is for a STATEMENT trigger. It will only fire once for each triggering statement. When using the STATEMENT type of trigger, the REFERENCING will use OLD_TABLE for all before image rows and NEW_TABLE for all after image rows.

The basic format and syntax to create a statement trigger:

To CREATE a trigger, a unique name must be chosen within a database for the new object. Then, chose a trigger action from the three types to dictate when the trigger will execute, they are: BEFORE, AFTER, and INSTEAD OF. The implication here regards when the trigger fires. It either fires BEFORE the user request, AFTER the user request or INSTEAD OF the user request.

The optional ORDER designation is used to sequence the firing of triggers. This is only a consideration when more than one trigger is based on the same trigger action (BEFORE, AFTER or INSTEAD OF). For instance, if there is one BEFORE and one AFTER trigger, then logically the BEFORE fires first and the AFTER fires last. For more information on ORDER, see the Sequencing Triggers section later.

Next, determine the action that is to cause the trigger to execute. There are four types of SQL triggering statements that can cause a trigger to fire, they are: INSERT, INSERT/SELECT, UPDATE, and DELETE.

When the triggering statement is an UPDATE, the optional OF portion is probably needed. It names one or more specific column(s) to monitor for a change in the data. If data, in all columns named in the OF, is changed the trigger will fire. Otherwise, if the data is changed in only one of the columns, the trigger does not fire. It is all or nothing regarding the columns being monitored for a change. When the OF is not used, the value in every column must change in order for the trigger to fire.

The table name specified by the ON is referred to as the subject table. It is the table to be changed by the triggering statement (user request). A trigger can only have one subject table.

Although there are both OLD AS and NEW AS references, they are only be used together when an UPDATE is the triggering statement. The UPDATE must have an existing row to modify. Therefore, a before image and a modified after image both exist.

When the triggering statement is an INSERT, there is only a NEW AS reference name because there is only a new row and not an old row. Conversely, when the triggering statement is a DELETE, there is only an OLD AS reference name because the row no longer exists.

The optional WHEN designator provides a last conditional test to make before actually executing the trigger. For instance, it might check the time of day or perform a subquery (only place a SELECT is allowed) to check for a specific value, such as an IN or NOT IN. As an example, a test might be performed to determine the amount of work involved using a COUNT and if too many rows are involved, delay the triggering statement until later. The trigger will fire only if the WHEN test compares true.

Lastly, determine the TRIGGERED ACTION. It is an SQL triggered statement that will execute as a result of the triggering statement. There are six SQL statements that can be a triggered statement, they are: INSERT, INSERT/SELECT, UPDATE, DELETE, EXEC and ABORT/ROLLBACK. Remember that a trigger is part of a transaction and cannot begin a new transaction.

‘BEFORE’ Trigger

The BEFORE trigger is executed prior to the maintenance requested by the user's client application. Once the trigger has successfully completed, the actual modification of the row(s) in the subject table is done for the user.

The following creates a BEFORE trigger on the Employee table to provide a Cascade form of RI by making sure that the Department table contains the department in which the new employee works:

This is a row trigger because it contains FOR EACH ROW. It is executed once for each row inserted into the Employee table and each triggered INSERT only inserts a single row if the department for the new employee is not a valid department verified against the department table. This request is not allowed if the subquery in the WHEN references the subject table. Therefore, the Valid_Dept_table is required to allow that test.

Other than checking another table, a BEFORE trigger might be useful for making an adjustment in another table before making the final modification in the subject table. In other words, if the update might cause a RI error, the trigger can prepare the other table to conform to an RI constraint before the original update gets rejected. For instance, if a Department were being deleted, the trigger could change the department number for all employees to NULL before the deletion of the department row. This is an example of the Nullify Strategy for RI.

‘AFTER’ Trigger

The AFTER trigger is executed or fired subsequent to the original update requested by the user's client application. Once the trigger has successfully completed, the user request part of the transaction is finished.

The following creates an AFTER trigger on the Parts table to insert an audit row into the Price Log table if the price is changed. However, the trigger only fires if the increase is greater than 10%:

This is also a row type of trigger. Therefore, it will be executed once for each and every row that is affected by the original update SQL request.

Here a WHEN determines whether or not the trigger fires. For small price increases, the trigger does not fire, but instead, only for a price change of more than 10%. Without a WHEN test, the trigger fires every time the triggering statement makes a change.

An AFTER trigger is helpful to make an adjustment in one table based on a change made to the subject table. For instance, if a new manager were assigned to a department in the department table, all the rows for employees are updated automatically to reflect the same change in the employee table. This is an example of the Cascade Strategy for RI.

‘INSTEAD OF’ Trigger

The INSTEAD OF trigger provides the ability to execute the trigger and not do the user's client application request at all. Once the trigger has successfully completed, the transaction is finished.

The following INSTEAD OF trigger is created on the parts table to insert an audit row into the delay table indicating that an UPDATE on the parts table should have taken place. However, because it was going to impact more than 10 rows in the subject table, it was not performed:

Since the update on the parts table was not performed, it probably needs to be made at another time. This actual update becomes someone's responsibility. This is a time when the flexibility to not make the change adds responsibility to manually make the change later. However, the rows in the delay table can be used via a join to actually make the update.

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

Teradata Topics