Physical Modeling › Triggers › Methods to Override Trigger Templates › Table Override Templates › Define a Table Override Trigger

Define a Table Override Trigger

You can use the Triggers dialog to define a table override trigger. Specify general information for a table override trigger, such as the action for which the trigger should fire, when the trigger should fire, how the trigger should fire, and so forth.

To define a table override trigger

1. Click Triggers, Table Triggers on the Database menu.

   The Triggers dialog opens.

2. Select a table and a trigger and complete the following information on the General tab:
   • Trigger On

     Specifies the SQL command executed for which the trigger should fire. When you select an option, you include the built-in trigger code for that action in the table trigger. The options are:

     • Delete
     • Insert
     • Update

       Note: You can create separate table override triggers for insert, update and delete actions, or you can combine different actions into a single trigger. If you want to combine actions, select each Trigger On check box (Delete, Insert, and Update). For example, if you want to extend the customized table override trigger "special order-line insert" to calculate the product_sold_quantity on update, delete, and insert of ORDER_LINE, select the Update, Delete and Insert check boxes. Then, change the SQL code to handle the conditions for recalculating product_sold_quantity under update and delete conditions.

   • Fire

     Select an option to specify when to fire the trigger. A macro for the specified option is included in the trigger template. This is not available for all databases.

     Before

     Fires the selected table trigger before the SQL INSERT, UPDATE, or Delete command is executed.

     Each Row

     Fires the selected trigger once each time a SQL INSERT, UPDATE, or Delete command is executed against a row in the table. You can use the %Scope macro to include the FOR EACH ROW clause in the trigger.

     After

     Fires the selected table trigger after the SQL INSERT, UPDATE, or Delete command is executed.

   • Scope

     Specifies how to fire the trigger. A macro for the specified option is included in the trigger template. Not available for all databases.

   • Old

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

   • New

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

   • Column(s)

     Lists all columns in the selected table for which an update to the column can be used as a triggering event. To fire the selected table trigger template when a SQL UPDATE command is executed on a column, select the column in the list and click the arrow to move the selected column in the Column(s) list to the Update of Column(s) list.

   • Update of Column(s)

     Lists all columns in the selected table for which an update to the column is a triggering event. Click the arrow to move the selected column in the Update of Column(s) list to the Column(s) list.

   • Parent

     Displays the name of the selected table (if it is the parent table in a relationship) or the parent of the selected table, for each relationship in which the selected table is involved.

   • Child

     Displays the name of the selected table (if it is the child table in a relationship) or the child of the selected table, for each relationship in which the selected table is involved.

   • Verb Phrase

     Displays the name of all relationships for the selected table.

   • Integrity Rule

     List the referential integrity rules that apply to the selected relationship. Rules are abbreviated and indicate the RI trigger action, such as CHILD UPDATE (CU:) and the RI type, such as RESTRICT (R).

   • Generate

     Generates a CREATE TRIGGER statement for the trigger in the schema generation script.

   Click OK.

   Your changes are saved and the Triggers dialog closes.

   Note: Click Cancel to close the dialog without saving your changes.

You can complete the fields on the Code, Expanded tabs and then optionally select the Comment or UDP tab to add a comment or user defined property.