Oracle trigger learning record

1. This is the rule of oracle and cannot operate on tables that execute triggers.

You can operate on new.xxx, but for oracle row-level triggers (for each row), you cannot do any operations on this table, including reading

Principles:

Access to this table can be achieved in before insert triggers

Access to this table cannot be implemented in after insert triggers

Access to this table cannot be realized in before/after update/delete triggers.

In fact, the reason is very simple, just to prevent dirty reading.

2. When writing oracle row-level triggers, the table cannot be operated, the report * * has changed, and the trigger / function cannot be read "

The reason is obvious that row-level trigger code cannot manipulate the table, including select, which is depressing.

Of course, the solution is based on the reason, because the operation of row-level triggers is limited, only table-level triggers can be selected, but table-level triggers can not get the values of new and: old, so we can only use two kinds of triggers, and package or temporary table to assist.

First, write the required value,: new or: old, to the package or temporary table in the row-level trigger

Then the data that has been written in the package or temporary table is processed in the table-level trigger, and the data of the temporary table can be deleted according to the requirements after successful operation.

3. ORACLE trigger

The syntax for ORACLE to generate database triggers is:

Create [or replace] trigger trigger name trigger time trigger event

On table name

[for each row]

Pl/sql statement

Where:

Trigger name: the name of the trigger object. Because the trigger is automatically executed by the database

So the name is just a name and has no real use.

Trigger time: indicates when the trigger is executed. This value is desirable:

Before--- indicates that the trigger executes before the database action

After--- indicates that the issuer executes after the database action.

Trigger event: indicates which database actions trigger this trigger:

Insert: database insert triggers this trigger

Update: database modification triggers this trigger

Delete: database deletion triggers this trigger.

Table name: the table where the database trigger is located.

For each row: execute once for each row trigger of the table. If it wasn't for this,

Option, it is executed only once for the entire table.