Oracle trigger type, oracle trigger

Oracle trigger type, oracle trigger

Http://www.cnblogs.com/roucheng/p/3506033.html

Triggers are a technology provided by many relational database systems. In the ORACLE system, triggers and similar procedures and functions have PL/SQL blocks that declare, execute, and handle exceptions.

1. Trigger type

A trigger is stored in a database as an independent object. Unlike stored procedures and functions, a stored procedure and function must be displayed and called before execution, A trigger is started by an event. That is, the trigger runs automatically and implicitly when an event occurs. In addition, the trigger cannot receive parameters. Therefore, running a trigger is called firing ). ORACLE events refer to the INSERT, UPDATE, and DELETE operations on database tables or similar operations on views. ORACLE extends the trigger function to ORACLE, such as database startup and shutdown. Therefore, triggers are often used to complete the constraints of complex business rules that are difficult to complete due to the integrity constraints of the database, or to monitor various operations on the database to implement the audit function.

 

1.1 DML triggers

ORACLE can trigger a DML statement before or after a DML operation, and can trigger each row or statement operation.

 

1.2 alternative triggers

In ORACLE, views created from more than two tables cannot be operated directly. Therefore, an alternative trigger is provided. It is a specific solution for view operations in ORACLE 8.

 

1.3 system triggers

ORACLE 8i provides a third type of trigger called a system trigger. It can be triggered in ORACLE Database System Events, such as ORACLE system startup and shutdown.

 

Trigger composition:

L trigger event: the event that causes the trigger to be triggered. For example, DML statements (INSERT, UPDATE, and DELETE statements that perform data processing on tables or views), DDL statements (such as CREATE, ALTER, and DROP statements that CREATE, modify, and DELETE schema objects in the database) database System events (such as system startup or exit, abnormal errors), user events (such as logon or database exit ).

L TRIGGER time: whether the TRIGGER is triggered BEFORE or AFTER the TRIGGER event, that is, the TRIGGER event and the TRIGGER operation sequence.

L TRIGGER operation: the purpose and intent of the TRIGGER after it is triggered, which is exactly what the TRIGGER is going to do. For example, PL/SQL block.

L trigger objects: including tables, views, modes, and databases. Only trigger events that meet the trigger conditions on these objects can be triggered.

L trigger condition: A logical expression is specified by the WHEN clause. The trigger is automatically executed only when the value of this expression is TRUE.

L trigger frequency: the number of times the action defined in the trigger is executed. STATEMENT-level (STATEMENT) triggers and ROW-level (ROW) triggers.

STATEMENT-Level Trigger: When a trigger event occurs, the trigger is executed only once;

ROW-Level Trigger: When a trigger event occurs, the trigger is executed independently for each ROW of data affected by the operation.

Note the following when writing a trigger:

L The trigger does not accept parameters.

L a table can have up to 12 triggers, but only one trigger of the same time, event, and type can have one. There must be no conflict between triggers.

L The more triggers on a table, the greater the impact on the performance of DML operations on the table.

L The maximum trigger size is 32 KB. If necessary, you can first create a process and then use the CALL statement in the trigger to CALL it.

LOnly DML statements (SELECT, INSERT, UPDATE, and DELETE) can be used for trigger execution. DDL statements (CREATE, ALTER, and DROP) cannot be used).

L The trigger cannot contain transaction control statements (COMMIT, ROLLBACK, SAVEPOINT ). Because the trigger is part of the trigger statement, when the trigger statement is submitted or rolled back, the trigger is also submitted and rolled back.

L no process or function called in the trigger body can use transaction control statements.

L you cannot declare any Long or blob variables in the trigger body. The new value and old value cannot be added to any long or blob columns in the table.

L syntax formats and functions OF different types OF triggers (such as DML triggers, instead of triggers, and system triggers) are significantly different.

Http://www.cnblogs.com/roucheng/p/3504463.html