Triggers Wizard (Oracle)

From RapidSQL
Jump to: navigation, search

Go Up to Oracle Object Wizards

This wizard lets you build and submit a CREATE OR REPLACE TRIGGER statement, creating a trigger to be associated with a table, view, schema, or database. BEFORE, AFTER, and COMPOUND triggers are supported for a variety of DDL, DML, and Database manipulation events.

Important Notes

  • To create triggers in your own schema, you need CREATE TRIGGER privileges. To create triggers in other schemas, you need CREATE ANY TRIGGER privileges.

To create a new trigger using a wizard:

  1. Open a creation wizard for a trigger. For details, see Opening an Object Wizard.
  2. Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
  3. Finally, use the Execute button to create the object.

Triggers (Oracle) - Properties

When creating or editing a trigger, this tab/panel lets you work with the following settings:

Setting Availability and description

Parent Type

Select the type (TABLE, DATABASE, SCHEMA, or VIEW) of element that this trigger is to be associated with.

Parent Schema, and Parent Name

If available and appropriate, select the specific object for which the trigger is being created.

Schema and Name

Select a schema and provide a name for the trigger being created.

Enabled

Deselecting this check box adds a DISABLE clause to the CREATE OR REPLACE TRIGGER statement, disabling the trigger.

Trigger Timing

BEFORE: These triggers serve as extensions to the constraint subsystem and are most often used to validate input data, generate values for newly inserted rows, and read from other tables for cross-reference purposes. Note: Before triggers must be created as a For Each Row. AFTER: Such a trigger is run after the integrity constraint validations; they can be used to modify operations in the database or be used for activities beyond the database, like supporting an alert notification. COMPOUND (11.1.0.7 ^): These triggers fire at more than one timing point.

Trigger Type

Not available with a Parent Type of Database or Schema and not available with a Trigger Timing value of COMPOUND

Selecting ROW adds a FOR EACH ROW clause to the CREATE OR REPLACE TRIGGER statement, making this a row trigger that fires for each affected row. Selecting STATEMENT designates this as a statement trigger that only fires once.

Old Table Alias

Not available with a Parent Type of Database or Schema

Type the name of a temporary table of rows as they exist before they are updated or deleted.

New Table Alias

Type a name for a temporary table of rows as they exist after they are inserted or updated.

When Clause

Type a WHEN clause or open a WHEN CLAUSE wizard to further qualify the trigger behavior.

Triggers (Oracle) - Events

When creating or editing a trigger, this tab/panel lets you provide BEFORE, AFTER, or FOR clause options.

If you selected a Parent Type of Table or View, select one or more of the INSERT, UPDATE, or DELETE check boxes to specify the events that are to fire the trigger.

If you selected a Parent Type of Database or Schema, select one or more of the ALTER, CREATE, ANALYZE, ASSOCIATE_STATISTICS, AUDIT, COMMENT, DISASSOCIATE_STATISTICS, DROP, GRANT, NOAUDIT, RENAME, REVOKE, TRUNCATE, LOGON, SERVERERROR, or SUSPEND check boxes to specify the events that are to fire the trigger.

Triggers (Oracle) - Column Selection

If you chose UPDATE as the Trigger Event, this tab/panel lets you provide FOR UPDATE OF, BEFORE UPDATE OF, or AFTER UPDATE OF clause options. Select the check box beside each column that is to fire the trigger.

Triggers (Oracle) - Action

If you selected a Trigger Timing value of BEFORE or AFTER, provide the complete body of trigger. Keep the following in mind:

  • If the SQL statement you have in mind for a trigger is longer than 60 lines of code, you would be better off creating a stored procedure.
  • INSTEAD OF statements can only be used by view triggers.
  • BEFORE and AFTER options cannot be used for view triggers.

If you selected a Trigger Timing value of COMPOUND, a compound trigger block template is generated. Customize the generated code provide a valid trigger body.