Syntax for creating triggers in Oracle

Creating triggers

The general syntax for creating triggers is:

CREATE [OR REPLACE] TRIGGER trigger_name

{before | After}

{INSERT | DELETE | UPDATE [of column [, Column ...]]}

[OR {INSERT | DELETE | UPDATE [of column [, Column ...]]} ...]

on [schema.] table_name | [Schema.] View_name

[Referencing {old [as] old | NEW [as] new| Parent as parent}]

[For each ROW]

[When condition]

Pl/sql_block | Call procedure_name;

which

Before and after indicate that trigger triggers are triggered in both front and rear triggers, which trigger the currently created trigger before the trigger is executed, triggering the trigger that is currently being created after the triggering event is executed.

The For each row option describes the trigger as a row trigger. The difference between a row trigger and a statement trigger is shown in: Row triggers require that when a DML statement moves multiple rows of data in a database, the trigger is activated once for each row of data, as long as they conform to the trigger constraint, and the statement trigger takes the entire statement action as the triggering event, and when it meets the constraint, Activates a trigger once. When the For each row option is omitted, the before and after triggers are statement triggers, and the instead OF triggers are only row triggers.

The referencing clause describes the correlation name, which can be used in the Pl/sql block and the When clause of the row trigger to reference the current new and old column values, with the default related names old and new. When you apply a correlation name to a pl/sql block of a trigger, you must precede them with a colon (:), but you cannot add a colon in the When clause.

The When clause describes the trigger constraint. When Condition is a logical expression, it must contain the relevant name, not the query statement, or call the Pl/sql function. The trigger constraint specified by the When clause can only be used in before and after-row triggers and cannot be used in instead of row triggers and other types of triggers.

The stored procedure to execute when a base table is modified (INSERT, UPDATE, DELETE) is automatically triggered according to the base table changes it relies on, so it is not relevant to the application, and database triggers are used to guarantee the consistency and integrity of the data.

Each table can establish up to 12 types of triggers, which are:

Before INSERT

Before INSERT for each ROW

After INSERT

After inserts for each ROW

Before UPDATE

Before UPDATE for each ROW

After UPDATE

After UPDATE for each ROW

Before DELETE

Before DELETE for each ROW

After DELETE

After the DELETE for each ROW

1 Trigger Trigger Order

1. Execute before statement level triggers;

2. For each row that is affected by the statement:

L Execute before row-level triggers

L Execute DML statements

L execute after row-level triggers

3. Execute AFTER statement level triggers

2 Creating a DML trigger

The trigger name is not the same as the name of the procedure and the package, and it is a separate namespace, so the trigger name can have the same name as the table or procedure, but the trigger name cannot be the same in a pattern.

Limitations of DML triggers

L CREATE Trigger Statement text character length can not exceed 32KB;

L The SELECT statement in the trigger body can only be select ... Into ... Structure, or the SELECT statement used to define the cursor.

L Trigger cannot use database transaction control statement COMMIT; ROLLBACK, Svaepoint statement;

L A procedure or function invoked by a trigger cannot use a database transaction control statement;

L triggers cannot use long, long RAW type;

L triggers can reference the column values of LOB type columns, but cannot pass: NEW modifies data in LOB columns;

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/