A script-enabled browser is required for this page to function properly.

Database Trigger editor

The Database Trigger editor enables you to create and edit BEFORE/AFTER triggers for tables, and INSTEAD OF triggers for views and object views (if connected to an Oracle database).

Table Owner

Displays the user ID of the owner of the current database table. Click the arrow to the right of this field to display a list of users.

Table

Displays the name of the current database table. click the arrow to the right of this field to display a list of table names of the tables owned by the user shown in the Table Owner field. Click a table in the list to select it. The Table list displays only the names of the database tables you have access to.

Name

Displays the name of the current database trigger. Click the arrow to the right of this field to display a list of trigger names for the table shown in the Table field. The Name list displays only the names of the database triggers you have access to.

Triggering (BEFORE and AFTER)

(For tables only.) Corresponds to the BEFORE or AFTER option of the SQL command CREATE TRIGGER.

Indicates when the trigger body is fired in relation to the triggering statement (for example, UPDATE, INSERT, or DELETE) that is being executed. BEFORE triggers are fired before the triggering statement. AFTER triggers are fired after the trigger statement is executed.

BEFORE and AFTER triggers apply to both row and statement triggers.

Instead of

(For views only.) Corresponds to the INSTEAD OF option of the SQL command CREATE TRIGGER.

Oracle fires the trigger instead of executing the triggering statement. The trigger performs the insert, update, or delete actions directly on the base tables of the view.

Statement

Specifies the type of SQL statement (triggering statement) that fires the trigger body. The possible options are UPDATE, INSERT, and DELETE.

Check one or more of the check boxes to include the option in the triggering statement.

If the triggering statement specifies UPDATE, an optional list of columns can be included in the triggering statement. See the Of Columns option next.

If more than one option is checked, you can use the conditional predicates UPDATING, INSERTING, and DELETING to execute specific blocks of code in the trigger body. See Trigger Body later.

Of Columns

(For tables only.) Corresponds to the OF clause in the UPDATE triggering statement of the SQL command CREATE TRIGGER.

Displays a list of columns for the table selected in the Table field. This list is active only if the triggering statement specifies UPDATE.

Click one or more columns in this list box to specify that the trigger fires only when one of the selected columns is updated. If you want the trigger to fire when any column in the table is updated, do not click any column names in the list.

Not available with INSTEAD OF triggers. Oracle fires INSTEAD OF triggers whenever an UPDATE changes a value in any column of the view.

Oracle database objects: You cannot specify nested table columns or LOB columns here.

For Each Statement

Corresponds to the FOR EACH STATEMENT option of the SQL command CREATE TRIGGER.

Designates the trigger to be a statement trigger. Oracle fires a statement trigger only once when the triggering statement is issued (regardless of the number of rows affected), if the optional trigger constraint defined in the WHEN clause is satisfied.

Not available with INSTEAD OF triggers.

For Each Row

Corresponds to the FOR EACH ROW option of the SQL command CREATE TRIGGER.

Designates the trigger to be a row trigger. Oracle 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 clause.

Referencing

Specifies correlation names. You can use correlation names in the trigger body and WHEN clause of a row trigger to refer specifically to old and new values of the current row. The default correlation names are OLD and NEW. If your row trigger is associated with a table named OLD or NEW, use this option to specify different correlation names to avoid confusion between the table name and the correlation name. For row triggers only.

OLD As

Specifies a correlation name to avoid a name conflict between the correlation name and a table that is named OLD.

NEW As

Specifies a correlation name to avoid a name conflict between the correlation name and a table that is named NEW.

When

(For tables only.) Corresponds to the Boolean SQL expression in the WHEN clause of the SQL command CREATE TRIGGER. For row triggers only.

Specifies an optional trigger restriction.

If included, the SQL expression is evaluated for each row that the trigger affects. If the expression evaluates to TRUE for a row, the trigger body is fired. If the expression evaluates to FALSE or NOT TRUE for a row (that is, unknown, as with nulls), the trigger body is not fired.

Oracle database objects: You can reference object columns or their attributes, VARRAY, nested table, or LOB columns. You cannot invoke PL/SQL functions or methods in the trigger restriction.

Trigger Body

The PL/SQL block Oracle executes to fire the trigger.

A scrollable, multiline text box where you can type and edit a PL/SQL block. Trigger bodies can include SQL and PL/SQL statements. BEGIN and END keywords are required.

The trigger body for row triggers can include correlation names and the REFERENCING option, and the conditional predicates INSERTING, DELETING, and UPDATING.

If more than one type of triggering statement is specified for the row trigger, for example, INSERT and UPDATE, you can include the following conditions to execute specific blocks of code in the trigger body:

IF INSERTING THEN…END IF;
IF UPDATING THEN…END IF;

New

Creates a new, unnamed database trigger.

Save

Saves the trigger options and compiles the source appearing in the Trigger Body field. Any error messages generated as a result of the compilation will appear in a separate dialog.

Revert

Restores the database trigger to its state at the last save or revert operation. If no save or revert operations have occurred, the database trigger is returned to its original state.

Drop

Drops the current database trigger from the database, with confirmation.

Close

Attempts to close the Database Trigger editor. If any changes have been made but not applied, an alert appears, prompting you to compile or revert the changes or cancel the operation. Once all changes have been compiled or reverted, the Database Trigger editor is closed.

See also

About database triggers

Creating a database trigger