Triggers in Oracle (1)

An Oracle database trigger is a named PL/SQL statement block stored in a database that is implicitly executed when the triggering event occurs, and the activity that executes the trigger is called the trigger trigger.

A particular user in a particular mode, or any user-executed DDL statement (such as Create or alter), is often used for auditing purposes and is dedicated to Oracle DBAs. You can record various schema modifications, when they are executed, and what the user is doing.

System events, such as database startup or shutdown

User events, such as logon or logoff. That is, you can define a trigger that logs the user name and logon time when the user logs on to the database.

Trigger-related views:

Common syntax for creating triggers:

create [or replace] trigger Trigger_name{before|after} triggering_event on table_name[for each row][follows another_ Trigger][enable/disable][when Condition]declare declaration statementsexceptionexception-bandling Statementsend;

Triggering_event is a DML statement for a database table, and TABLE_NAME is associated with that trigger. the name of the database table, the clause for each row, specifies the row trigger, only applicable to the inserted, modified, or the number of rows to delete. The When clause specifies the conditions that must be met when the trigger is executed. This part of the trigger is called the head of the trigger.

Clauses follows, enable, disable, which are in the Oracle 11g Create or replace trigger

clause to add the. You need to use the ALTER TRIGGER command to enable or disable triggers before 11.

The enable/disable clause Specifies whether the trigger is created in the enabled or disabled state. When the trigger

is enabled, the trigger is executed when the triggering event occurs, similar to the trigger when the trigger is disabled

The trigger is not executed when the event occurs.

Note By default, when you create a trigger without using the enable/disable clause, it is enabled by default.

ALTER TRIGGER trigger_name disable; ALTER trigger trigger_name enable;

using the follows option, you can specify the order in which triggers are triggered, and this option applies to the A trigger that is defined and executed at the same point in time. For example, if you define two triggers on a student table and trigger before the data is inserted, and if you do not use the follows clause to specify the order of execution , Oracle cannot guarantee that these triggers will always be executed in the same order, note that The trigger referenced in the follows clause must already exist, and the compilation succeeds.

Note: If you delete a table, the database triggers that are defined on the table are also deleted.

Triggers can be used for different purposes:

1. Performing complex business rules that cannot be defined by using integrity constraints

2. Maintaining complex security rules

3. Automatically generate values for derived columns

4. Mobile phone statistics about accessing database tables

5. Preventing Invalid transactions

6. Provide value audits

Limitations OF Triggers

1. Triggers may not execute transaction control statements, such as Commit, savepoint, rollback. When

When a trigger executes, all operations that are performed become part of the transaction. When the transaction is committed or rolled back

, the actions performed by the trigger are either committed or rolled back. An exception to this rule is the inclusion of autonomy

The trigger for the service.

2. Any function or procedure invoked by a trigger may not execute a transaction control statement unless it contains an autonomous transaction

3. Do not allow a long or long raw variable to be declared in the trigger body

Categories of triggers: Before triggers and after triggers

Before Trigger

create or replace trigger student_bibefore  Insert on studentfor each rowdeclarev_student_id student.student_in%type ;begin    select student_id_seq.nextval   into v_student_id    From dual ;  :new.student_id := v_student_id ;  :new.created_by  := user ;  :new.created_date := sysdate ;  :new.modified_by  := user ;  :new.mocified_date := sysdate ;end ; 

triggers contain pseudo-records: New, which allows access to the rows of data that are currently being processed, which means The data row that is currently inserted into the student table. : The new pseudo-record is a triggering_table%type, so in the current case it is the Student%type type, in order to access the pseudo record: New's separate member, need to use dot notation, That is: New.created_by refers to the member created_by of the new pseudo record , the point symbol used between the record name and its members.

A statement that assigns a sequence value to a student_id column that accesses a sequence with a PL/SQL expression is a new feature of 11G

Before 11G, the sequence can only be accessed through a query.

The before trigger should be used as follows

1. When the INSERT or UPDATE statement is complete, the trigger needs to provide the value of the derived column.

For example, the Final_grade column in the enrollment table holds the final grade for the student's specific course, which

Comprehensive performance from students throughout the course

2. When a trigger determines whether an insert, update, or DELETE statement should be allowed to complete, such as

When a record is inserted into the instructor table, the trigger can verify that the value supplied to the Zip column is valid.

Or, in other words, whether there is a record for the entire value in the ZipCode

After triggers

The structure of a statistics table is as follows

Name Null? Type-----------------------------------------------------------------------------table_name VARCHAR2 (30) Transaction_name VARCHAR2 (Ten) Transaction_user VARCHAR2 (transaction_date DATE)

The entire table is used to collect statistics for different tables in the database, for example, to record who from the Instractor table Delete records, and the time of deletion.

create or replace trigger instructor_aud   AFTER UPDATE OR DELETE ON INSTRUCTORDECLARE  V_TYPE VARCHAR2 (ten); begin   if updating then    v_type :=  ' UPDATE ';     elsif deleting then    v_type :=  ' DELETE ';  end  If;  update statistics     set transaction_user = user,  transaction_date = sysdate   where table_name =  ' instructor '      and transaction_name = v_type;  if sql%notfound% type then    insert into statistics values  (' instructor ',  v_ type, user, sysdate);   end if;end; 

For table instructor, triggers are triggered after the table update or DELETE statement executes.

You should use the After trigger if the following is the case

1. When the trigger should be triggered after the DML statement is executed.

2. When the trigger executes an action that is not specified in the Before trigger.

Detailed operation see next .....

This article is from "The girl said" blog, please be sure to keep this source http://sugarlovecxq.blog.51cto.com/6707742/1682287

Triggers in Oracle (1)