"Plsql" trigger trigger type, status, number of references

 ************************************************************************* * * * Original:blog.csdn.net/clark_xu Xu Changliang's Column************************************************************************TriggerTrigger

When you want to do a DML operation. You do not need to provide a primary key value. The system generates its own initiative

The composition of a DML trigger consists of four parts:

Part:

1. trigger time, descriptive narrative: time Order of trigger time, possible value:after,before

2. triggering event, descriptive narration:DML statement is trigger time, possible value insert,update,delete

3. trigger type. Descriptive narrative: The number of times a trigger has been run, possibly a value statement,row

4. trigger body, describe the action that the trigger will run, possibly a full plsql block

3.9.1DMLTypes of Triggers

Statement-level triggers, row-level triggers,

The difference between a row-level trigger and a statement-level trigger:

1. The number of triggers is different, assuming that the DML statement affects only one row, and the statement-level and row-level triggers are the same.

2. Assuming that multiple rows are affected, the number of row-level triggers is triggered more than the number of statement-level triggers;

3.9.2 DMLTrigger Order of Triggers

Depending on the time of the trigger, the type can be combined into four DML triggers,

1. Run one time before the SQL statement runs:

a) trigger time : Before

b) level statement

2. each record affected by the SQL statement is run once by Uupdate,delete or insert

a) trigger time:before

b) level:row

3. each record affected by the SQL statement is run once by Update,delete or insert

a) trigger time: after

b) basic:row

4. run once after the SQL statement runs

a) promotion time: After

b) level:statement

3.9.2 DMLRow-level triggers

the For each row clause creates a row-level trigger that is triggered on each line that is affected by the trigger event, such as:

Create or Replace Trigger Trig_name

{before |after} Insert [or update ...]

On Tab_name

For each row [when Restricting_condition]

Plsql Block;

3.9.3Row-level triggers: OldAnd:NEW

Precede the column name with an old identifier that represents the value before the column changes, plus: TheNEW identifier represents the changed value.

So trigger the event:

1. Insert event,: Old. Column name (all fields are NULL):NEW. Column name ( the value to insert in the INSERT statement )

Note: (Stored procedure:Oracle:plsql. pro*c, the value of the database is removed for operation since. Stored procedures to be stored in the database)

2. Update event , old. The column name (the original value of the column before the update ). NEW. Column name ( the new value for the UPDATE statement to be finer ),

3. Delete event. Old . Column name (the original value at the top of thedelete row),NEW. Column name (all fields are null);

3.9.4Another compilation of triggers

Assume that other functions or procedures are called within a trigger, and when they are deleted or altered, the state of the trigger is recognized as invalid. When a DML statement activates an invalid trigger. Oracle will compile the trigger code again, assuming that errors are found at compile time, which will cause the DML statement to fail to run.

Manual Compilation:

Alter TRIGGER [schema.] Trig_name compile;

2.9.5Trigger State

Active Status:enable:

Invalid Status:disable

Conversion of two states:

Alter TRIGGER Trig_name [disable | enable];

The ALTER TABLE statement changes the usage state of all triggers associated with the specified table at once;

ALTER TABLE [schema.] Tab_name {Enable |disable} all triggers;

************************************************************************* * * * Original:blog.csdn.net/clark_xu Xu Changliang's Column************************************************************************

"Plsql" trigger trigger type, status, number of references