Introduction to Oracle triggers and database triggers

Triggers are a technology provided by many relational database systems. In Oracle systems, triggers are similar to processes and functions, with pl/sql blocks for declaration, execution, and exception handling processes.

1 Trigger Type

Triggers are stored as separate objects in the database, unlike stored procedures and functions, stored procedures and functions require a user to display the call to execute, and triggers are started by an event. That is, triggers are automatically implicitly run when an event occurs. Also, triggers cannot receive parameters. So running triggers are called triggers or ignition (firing). An Oracle event refers to an insert, update, and delete operation on a database table or a similar operation on the view. Oracle extends the functionality of triggers to trigger 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 by the integrity constraints of the database, or to monitor various operations of the database to achieve audit functions.

1.1 DML triggers

Oracle can trigger on DML statements, which can be triggered before or after a DML operation, and can be triggered on each row or statement operation.

1.2 Override triggers

Because in Oracle, you cannot directly manipulate views that are established by more than two tables. Therefore, an alternative trigger is given. It is Oracle 8, a processing method specifically for view operations.

1.3 System triggers

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

Trigger Composition:

L Trigger Event: The event that triggers the trigger to be triggered. For example, a DML statement (INSERT, UPDATE, DELETE statement performs a data processing operation on a table or view), a DDL statement (such as Create, ALTER, DROP statement creates, modifies, deletes a schema object in the database), database system events (such as system startup or exit, Exception error), user events (such as logging on or exiting the database).

L Trigger Time: The trigger is triggered before (before) or after the triggering event, that is, the triggering event and the order of operation of the trigger.

L Trigger action: that is, the purpose and intent of the trigger after it is triggered is what the trigger itself is going to do. For example: Pl/sql block.

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

L Trigger Condition: Specify a logical expression by the When clause. A trigger event is automatically executed when the value of the expression is true, causing it to perform the triggering action.

L Trigger Frequency: Describes the number of times the action defined within the trigger is executed. The statement level (STATEMENT) trigger and the row-level (row) trigger.

A statement level (STATEMENT) Trigger: A trigger event that is executed only once when a trigger is occurring;

Row-level triggers: The triggers are executed once for each row of data that is affected by the action when a trigger event occurs.

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

L triggers do not accept parameters.

L A table can have up to 12 triggers, but at the same time, the same event, the same type of trigger can have only one. 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 the table.

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

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

L triggers cannot contain transaction control statements (Commit,rollback,savepoint). Because triggers are part of the trigger statement, triggers are committed and rolled back when the trigger statement is committed and backed up.

L The Transaction control statement cannot be used for any procedure, function, invoked in the trigger body.

L cannot declare any long and BLOB variables in the trigger body. The new value, the old value, and the OID cannot be made to any long and BLOB columns in the table.

L The syntax format and function of different types of triggers, such as DML triggers, INSTEAD of triggers, and system triggers, differ considerably.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/