ORACLE trigger: common usage

Trigger is similar to procedure and function, but it cannot be displayed and called. It can only be triggered by an event and then automatically called by Oracle. generally, you can create a trigger for a table or view and trigger a trigger when you perform some operations on the table or view. of course, there are schema and database-level triggers.

 

Trigger trigger

Common statements are DML (insert, update, delete) and DDL (create, alter, drop) statements.

The schema-Level Trigger is triggered when the session is connected or disconnected. The database-Level Trigger is triggered when the system starts or exits.

You may easily find that trigger cannot be used for SELECT query operations, but sometimes we want to monitor who has checked some sensitive information. At this time, we can only use one thing called FGA, you can create an audit policy (which can be seen as an enhanced trigger, FGA introduction see: http://blog.csdn.net/weiwenhp/article/details/7165660)

 

 

Precautions for using triggers

1. The trigger does not accept parameters.,A table can have up to 12 triggers.(The trigger type is exactly 12). At the same time, for the same event, only one trigger of the same type can exist (ensure that trigger operations do not conflict with each other ).

2. The maximum trigger size is 32 KB.Because the size is limited, long, blob and other large variables cannot be used. if there is really a complicated logic, to create a complicated trigger, you can use procedure or function to implement some functions and then call

3.Because triggers can be considered as part of a trigger statement, some constraints must be followed.For example, you cannot have transaction control statements (commit, rollback, savepoint) or DDL statements. why? The major difference between these special statements and general SQL statements involves the issue of commit. therefore, if the trigger statement is only a general statement, it cannot be because the trigger operation has the commit feature.

 

Create a trigger

The trigger format for tables or views is as follows:

 

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;

 

Let's take a look at how to create a trigger at the statement level. If there is a table tb1, each insert point records some information in the tblog through the trigger.

Create or replace trigger tb1_trigger

After insert on tb1

Referencing new as new
Old as old

 

Declare

V_info varchar2 (100 );

Begin

V_info: = "Do A Insert ";

Insert into tblog (Info) values (v_info );

End;

 

The trigger creation has a problem that is not easy to understand: a new question about the old value of the row-Level Trigger

New Value of the row-Level Trigger old value

Trigger at the statement and row levels when you create a trigger for a table or view. the statement level means that an SQL statement triggers a trigger once. If the row level means that an SQL statement involves multiple rows of data, the trigger is triggered multiple times.

The old value refers to the value of the row of data to be changed before it is changed. The new value refers to the value after the user updates it. assume that the TT table has only one row of data: 88. then the user executes the statement update TT Set ID = 99 Where id = 88;

The old value is 88, and the new value is 99. Then you may ask how to get the old value or the new value.

Suppose there are tables Tb (eno int); and tables tblog (Info varchar2 (100); If you create a trigger on TB, once every TB update, the new value after the change is recorded in the tblog.

 

Create or replace trigger tb_trigger

Before update

On TB

Referencing new as new_val old as old_val -- set the name here, and then reference the new value, old value. If the default value is not specified as new, old. can be referenced through: New or: Old

For each row

Declare

V_info varchar2 (100 );

Begin

V_info: = 'old value: '| to_char (: old_val.eno) | 'new value:' | to_char (: new_val.eno );

Insert into tblog values (v_info );

End;

 

Condition judgment

If a trigger is triggered only when a row of operations are involved, if the trigger is triggered for updat, delete, and insert operations, which of the following statements can be used to determine the trigger.

For example

Create or replace trigger tb_trigger

Before update or insert or delete

On TB

Referencing new as new_val old as old_val -- set the name here, and then reference the new value, old value

For each row

When (old_val.eno = 99)

Declare

V_info varchar2 (100 );

Begin

 

Case

When updating then

V_info: = 'old value: '| to_char (: old_val.eno) | 'new value:' | to_char (: new_val.eno );

Insert into tblog values (v_info );

 

When inserting then

NULL;

 

When deleting then

NULL;

 

End case;

End;