Oracle---Triggers

first, the concept of triggers

triggers are stored as separate objects in the database, unlike stored procedures and functions, where stored procedures and functions require user-displayed calls to execute, and triggers are initiated by an event that is automatically run implicitly when an event occurs . Also, the trigger cannot receive parameters. So running the trigger is called trigger or ignition (firing).

Ii. types of Triggers

1.DML Trigger

ORACLE can trigger on DML statements , trigger before or after DML operations, and trigger on each row or statement operation.

2. Alternative triggers

since the in ORACLE, you cannot directly manipulate views created from more than two tables . Therefore, an alternative trigger is given. It is a processing method that ORACLE specifically provides for view operations.

3. System triggers

ORACLE 8i provides a third type of trigger system trigger. It can be triggered in the event of an Oracle database system, such as the startup and shutdown of an Oracle system.

Third, the creation of triggers

1. Trigger Composition

Triggering Events : The event that caused the trigger to be triggered.

For example: DML statements (INSERT, UPDATE, DELETE statements perform data processing operations on a table or view ),DDL statements (such as CREATE, alter,drop Statements Create, modify, delete schema objects in the database, database system events (such as system startup or exit, exception errors), user events (such as logging on or exiting the database).

Trigger Time : that the Whether the TRIGGER is triggered before the triggering event (before) or later (after ), that is, the sequence of actions that triggered the event and the TRIGGER .

Trigger Action : that the the purpose and intent of the TRIGGER after being triggered is exactly what the trigger itself is going to do.

For example: PL/SQL blocks.

Trigger Object : Includes tables, views, schemas, databases. Trigger actions are performed only if a trigger event that matches the trigger condition occurs on these objects.

Trigger Conditions : by The When clause specifies a logical expression. Only if the value of the expression is TRUE , the trigger event is encountered to perform the triggering action.

Trigger Frequency : Describes the number of times the action defined within the trigger is executed. That is, statement-level (STATEMENT) triggers and row-level (rows ) triggers.

Statement-level (STATEMENT) Trigger: Refers to when a trigger event occurs, the trigger executes only once;

Row Level (ROW) Trigger: The trigger is executed once for each row of data affected by the operation when a triggering event occurs.

When writing triggers, you need to be aware of the following points:

(1) The trigger does not accept parameters.

(2) There can be up to a maximum of two triggers on a table , but only one for the same time, the same event, the same type of trigger, and there is no contradiction between the triggers.

(3) The more triggers on a table, the more on the table the performance impact of DML operations is greater.

(4) The maximum trigger is 32KB. If you do, you can create a procedure and then invoke it in a trigger with a call statement.

(5) The execution portion of the trigger can only be used DML statements (SELECT,INSERT,UPDATE,DELETE), you cannot use DDL statements ( CREATE,ALTER,DROP).

(6) cannot contain transaction control statements in triggers (COMMIT,ROLLBACK,savepoint) . Because the trigger is part of the triggering statement, the trigger is committed and rolled back when the trigger statement is committed and rolled back.

(7) You cannot use transaction control statements for any procedure or function that is called in the body of a trigger.

(8) in the trigger body, you cannot declare any Long and blob variables. The new value , old , and both cannot be any long and blob columns in the table .

(9) different types of triggers ( such as DML triggers,INSTEAD of triggers, system triggers ) has a large difference in the syntax format and function.

2. Creation of triggers

Example 1: Front trigger

Create or Replace Trigger Tr_del_emp

Before delete

Onemp

foreach row

Begin

Insert into Emp_his (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)

VALUES (: Old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);

End

This article is from the "Notes" blog, so be sure to keep this source http://sunflower2.blog.51cto.com/8837503/1614127

Oracle---Triggers