"Plsql" trigger trigger type, state, parameter

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

You do not need to provide a primary key value when you do a DML operation, and the system automatically generates

The composition of a DML trigger consists of four parts:

Part:

1. Trigger Time, Description: Time order of trigger time, possible value:after,before

2. Trigger Event, Description:DML statement is trigger time, possible value insert,update,delete

3. trigger Type, Description: Number of times the trigger was executed, possible value statement,row

4. The trigger body, which describes the action that the trigger will perform, the plsql block that may be full value

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, and if the DML statement affects only one row, the statement-level and row-level triggers are the same;

2. If 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, different types can be combined into four DML triggers,

1. execute once before the SQL statement executes:

a) trigger time : Before

b) level statement

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

a) trigger time:before

b) level:row

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

a) trigger time: after

b) basic:row

4. execute once after the SQL statement executes

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, to be stored in the database with stored procedures)

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.4Recompilation of Triggers

If other functions or procedures are called within a trigger, the state of the trigger is recognized as invalid when they are deleted or modified, and when the DML statement activates an invalid trigger,Oracle will recompile the trigger code and, if it finds an error at compile time, will cause DML statement execution failed;

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, state, parameter