Trigger Designer

Creating Triggers in Oracle Developer Tools

Use the Trigger Designer to create triggers for tables or views. After you create a new trigger, you can edit the trigger specification using the PL/SQL Code Editor. When you create a trigger, Oracle Database automatically enables it.

You cannot use the Trigger Designer to create the following types of triggers:

Procedure calls as triggers
Nested column triggers if the new version of the trigger refers to the same view as the old version of the trigger
Triggers that use the:OLD, :NEW, or :PARENT reserved words for row values
The body of a trigger
DDL triggers

However, after you create the trigger, you can modify it in the PL/SQL Code Editor to use these features.

Starting the Trigger Designer

To start the Trigger Designer:

If you are creating the trigger on a table, in Server Explorer, open the node for the table. The table node lists nodes for the table's constraints, indexes, and triggers. Right-click the Triggers node and from the menu, select Add Trigger.
If you are creating the trigger on a view, in Server Explorer, select and open the view's node. Right-click the Triggers node and from the menu, select Add Trigger.

The Trigger Designer appears similar to the following:

Description of the illustration add_trigger.gif

Using the Trigger Designer

In general, use the Trigger Designer to create a new trigger. If you want to create the trigger with functionality not provided in the Trigger Designer, such as adding the OR REPLACE keyword, or if you want to edit the trigger later, you can modify the trigger in the PL/SQL Code Editor.

The Trigger Designer has the following controls:

Control	Description
Schema name	Select from the list of available schemas in which to create the trigger.
Trigger name	Enter a name for the trigger. The default name is based on the table for which the trigger was created.
Occurrence	Select from the following: BEFORE: Fires the trigger before executing the triggering event. For row triggers, it fires the trigger before each affected row is changed. AFTER: Fires the trigger after executing the triggering event. For row triggers, it fires the trigger after each affected row is changed.
Frequency	Select from the following: ROW LEVEL: Specifies the trigger as a row trigger. Oracle Database fires a row trigger once for each row that is affected by the triggering statement and meets the optional trigger constraint defined in the `WHEN` condition setting. STATEMENT LEVEL: Specifies the trigger as a statement trigger. Oracle Database fires a statement trigger only once when the triggering statement is issued if the optional trigger constraint is met.
Event type	Check one or more of the following: INSERT: Fires the trigger whenever an `INSERT` statement adds a row to a table or adds an element to a nested table. UPDATE: Fires the trigger whenever an `UPDATE` statement changes a value in one of the columns specified in the Select columns to apply to trigger list. If you do not select a column, the database fires the trigger whenever an `UPDATE` statement changes a value in any column of the table or nested table. If you checked `UPDATE`, the Columns to Update control is enabled. Note: You can specify object type, varray, and `REF` columns in the Select columns to apply to trigger list to fire the trigger whenever an `UPDATE` statement changes a value in one of the columns. However, you cannot change the values of these columns in the body of the trigger itself. DELETE: Fires the trigger whenever a DELETE statement removes a row from the table or removes an element from a nested table
Columns to update	Applies only to update triggers. Select from the columns to which the update trigger will apply.
WHEN condition	Enter a SQL condition that must be satisfied in order for the trigger to fire. This condition must contain correlation names and cannot contain a query.
Preview SQL	Displays the `CREATE` `TRIGGER` SQL command code in a pop-up dialog box and in the output window. Read-only.
OK	Saves your work and displays the trigger code in the PL/SQL Code Editor so that you can customize it. To commit the trigger, click the Save button in the Visual Studio .NET toolbar or select Save from the File menu. After you successfully create the trigger, Oracle Developer Tools displays its node in Server Explorer. If you have created the trigger with errors, Oracle Developer Tools displays an error dialog box, and then displays the error messages in the Output window. Click OK and correct the error in the dialog box. Afterwards, when you click the Trigger Designer's OK button to save your changes, click Yes to replace the incorrect trigger with the corrected version. Note: If a trigger produces compilation errors, it is still created, but it fails on execution. This means it effectively blocks all triggering DML statements until it is disabled, replaced by a version without compilation errors, or dropped. You can see the associated compiler error messages in the Oracle Output pane in the Output window.