Physical Modeling › Teradata Physical Property Editors › Define Teradata Table Triggers

Define Teradata Table Triggers

A trigger is a stored procedure that executes in response to an event. For example, a trigger can be executed when a row in a table is inserted, updated, or deleted. The trigger tells the DBMS how to process the SQL INSERT, UPDATE, or Delete commands to enforce business rules. You can use the Trigger editor to define the stored procedures that exist for the selected tablein a Teradata physical model.

To define Teradata table triggers

1. Select the table you want to work with, then click Triggers, Table on the Database menu.

   The Table tablename Trigger Editor opens for the table you selected.

2. Click New on the toolbar.

   A new trigger is added. Work with the following options:

   • Name

     Specifies the name of the trigger.

   • Database

     Specifies the database or user to which the trigger belongs. Select from the drop-down.

   • Insert or Update

     Select to execute trigger on insert or update. Make one selection only.

   • Delete

     Select to execute trigger on delete.

   • Fire

     Specify when trigger is executed: After, Before, For Each Row, For, Instead Of.

   • Trigger Fire Order

     Specifies the order to fire the triggers.

   • Generate

     Generates DDL for this object during Forward Engineering.

3. Work with the following fields on the General tab:
   • Use Replace Syntax

     Select to specify that Forward Engineering will use the REPLACE command instead of the CREATE command.

   • Enabled State

     Specifies whether the trigger is enabled or disabled. Select from the drop-down list.

   • Scope

     Specifies the scope of the trigger. Select from the drop-down list. Valid values are: Row or Statement.

   • New

     Indicates the name that you want to use to reference the post-change data in the table. You can use the %RefClause macro to include the REFERENCES clause in the trigger.

   • New Table

     Specifies the name for the new row.

   • Old

     Indicates the name that you want to use to reference the pre-change data in the table. You can use the %RefClause macro to include the REFERENCES clause in the trigger.

   • Old Table

     Specifies the name of the old row.

   • Trigger When

     Specifies the condition on when to trigger.

   • Trigger Update

     When you select to execute the trigger on update, this pane becomes active.

     Displays the valid columns that can have a trigger fired when updated. Select the appropriate columns.

   • Referential Integrity Constraints

     Displays the relationships and tables associated with the trigger.

4. Work with the other tabs in the editor to access additional design features, for example:
   • Edit the SQL code related to the selected trigger on the Code tab
   • Display fully expanded code on the Expanded tab
5. (Optional) Click the Comment tab and enter any comments you want associated with the trigger.
6. (Optional) Click the UDP tab to work with user-defined properties for the trigger.
7. Click Close.

   The Table Trigger Editor closes.