Oracle Trigger Type

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

Triggers are a technique that is provided by many relational database systems. In Oracle systems, trigger-like procedures and functions have a PL/SQL block that declares, executes, and processes exceptions.

1 Trigger Type

Triggers are stored as separate objects in the database, unlike stored procedures and functions, where the stored procedure and function require the user to display the call to execute, and the trigger is run by an event to start. That is, a trigger is automatically run implicitly when an event occurs. Also, the trigger cannot receive parameters. So running the trigger is called trigger or ignition (firing). An Oracle event refers to an insert, update, and delete operation on a table in a database or a similar operation on a view. Oracle extends the capabilities of triggers to trigger Oracle, such as database startup and shutdown. So the trigger is often used to complete the constraints of complex business rules that are difficult to complete by the integrity constraints of the database, or to monitor various operations on the database to realize the function of auditing.

1.1 DML triggers

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

1.2 Alternative triggers

Because in Oracle, it is not possible to operate directly on a view that is established by more than two tables. Therefore, an alternative trigger is given. It is an approach that Oracle 8 is designed to handle for view operations.

1.3 System triggers

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

Triggers consist of:

L Trigger Event: 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 in a database, modify, delete schema objects), database system events (such as system startup or exit, Exception errors), user events (such as logging in or exiting the database).

L Trigger Time: that is, 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.

L Trigger Action: The purpose and intent of the trigger after it is triggered is exactly what the trigger itself is going to do. For example, PL/SQL blocks.

L Trigger object: Includes table, view, schema, database. Trigger actions are performed only if a trigger event that matches the trigger condition occurs on these objects.

L Trigger Condition: A logical expression is specified by the When clause. Only if the value of the expression is true, the trigger event is encountered to perform the triggering action.

L 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: The trigger executes only once when a trigger event occurs;

Row-level triggers: A 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:

L triggers do not accept parameters.

L can have up to 12 triggers on a table, but only one at the same time, the same event, and the same type of trigger. And there can be no contradiction between the triggers.

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

L trigger Max is 32KB. If you do, you can create a procedure and then invoke it in a trigger with a call statement.

L can only use DML statements (SELECT, INSERT, UPDATE, DELETE) in the execution portion of the trigger, and cannot use DDL statements (CREATE, ALTER, DROP).

L cannot include transaction control statements (Commit,rollback,savepoint) in triggers. 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.

Any procedures, functions that are called in the body of the trigger cannot use transaction control statements.

L cannot declare any long and BLOB variables in the body of the trigger. The new and old values of the same cannot be any long and BLOB columns in the table.

L Different types of triggers (such as DML triggers, INSTEAD of triggers, system triggers) have a large difference in syntax format and function.

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

Oracle Trigger Type