(eight) Oracle Learning notes-triggers

1. Introduction to Triggers

Triggers are stored in the database as separate objects, similar to procedures (procedure) and functions, and have PL/SQL blocks that declare, execute, and process exceptions. Unlike stored procedures and functions, stored procedures and functions require the user to display the call before execution, 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.

2. Trigger Creation syntax

CREATE [OR REPLACE] TRIGGERTrigger_name{before|After } {INSERT | DELETE | UPDATE [of column [, column ...]]}[OR {INSERT | DELETE | UPDATE [of column [, column ...]]}...] on [schema.]table_name| [schema.]view_name[referencing {old [ as]Old|NEW[ as]New|PARENT asParent}][For each ROW][When condition]PL/Sql_block|Call procedure_name;

2.1 Statement Interpretation

Before and after indicate that the trigger's trigger timing is pre-and post-trigger, respectively, before triggering the trigger, which triggers the currently created trigger before executing the triggering event, triggering the trigger that is currently created after the trigger event is executed.

The For each row option describes the trigger as a row trigger. The difference between a row trigger and a statement trigger is that a row trigger requires that when a DML statement operation affects multiple rows of data in a database, the trigger is activated for each row of data, as long as they conform to the trigger constraint, and the statement trigger takes the entire statement action as a trigger event, and when it meets the constraints, Activates the trigger once. When the For each ROW option is omitted, the before and after triggers are statement triggers, whereas the instead OF triggers are only row triggers.

The referencing clause describes the correlation name, which can be used to refer to the current new and old column values in the PL/SQL block and when clauses of a row trigger, with the default correlation name of both OID and new. When you apply a related name in a PL/SQL block of a trigger, you must precede them with a colon (:), but you cannot add a colon in the When clause.

The When clause describes the triggering constraint condition. When Condition is a logical expression, it must contain a correlation name, not a query statement, or a PL/SQL function. The trigger constraint specified by the When clause can only be used in before and after row triggers, not in instead of row triggers and other types of triggers.

The stored procedure to execute when a base table is modified (INSERT, UPDATE, DELETE) is automatically triggered based on the base table changes it is attached to, so it is independent of the application, and database triggers are used to guarantee the consistency and integrity of the data.

2.2 Each table can have up to 12 types of triggers, which are:

BeforeINSERTbeforeINSERT  forEach rowafterINSERT AfterINSERT  forEach ROW beforeUPDATEbeforeUPDATE  forEach rowafterUPDATE AfterUPDATE  forEach rowbeforeDELETEbeforeDELETE  forEach rowafterDELETE AfterDELETE  forEach ROW

3. Trigger composition

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).

Trigger Time : That is, whether the trigger is triggered before the triggering event (before) or after (after), that is, the sequence of actions that triggered the event and the trigger.

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.

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.

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.

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.

4. Trigger Writing considerations

The trigger does not accept parameters.

There can be up to 12 triggers on a table, but only one for 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.

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

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

The trigger cannot contain a transaction control statement (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.

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

You cannot declare any long and BLOB variables in the body of the trigger. The new value, old, and both cannot be any long and BLOB columns in the table.

Different types of triggers, such as DML triggers, INSTEAD of triggers, system triggers, have a greater difference in the syntax format and function.

(eight) Oracle Learning notes-triggers