__oracle and usage of Oracle triggers

Directory (?) [+] trigger-type DML triggers need to be aware of the following when writing triggers instead OF trigger system triggers create a trigger trigger trigger order Create a DML trigger create an alternate instead of trigger create a system Event Trigger system trigger event property use a trigger verb to recompile a trigger delete In addition to enabling trigger triggers and data dictionary database triggers application example database triggers application instance trigger type DML triggers override trigger system triggers note the following when writing triggers create a trigger trigger trigger order Create a DML trigger create an alternative to instead OF triggers Create system event Triggers system triggers event properties use trigger predicates to recompile triggers delete and enable trigger triggers and data dictionary database triggers application examples of database triggers

The main contents of this article are as follows:

8.1 Trigger Type

8.1.1 DML triggers

8.1.2 override triggers

8.1.3 system triggers

8.2 Creating triggers

8.2.1 Trigger Trigger Order

8.2.2 Create a DML trigger

8.2.3 Create substitution (INSTEAD of) triggers

8.2.3 Create system Event triggers

8.2.4 System trigger Event properties

8.2.5 using trigger predicates

8.2.6 RECOMPILE triggers

8.3 Delete and enable trigger

8.4 Triggers and data dictionaries

8.5 Application Examples of 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. 8.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.

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

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

8.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.
8.2 Creating triggers

the general syntax for creating triggers is:

CREATE [OR REPLACE] TRIGGER trigger_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 as parent}]
[For each ROW]
[When condition]
Pl/sql_block | Call procedure_name;

which

Before and after indicate that trigger triggers are triggered in both front and rear triggers, which trigger the currently created trigger before the trigger is executed, triggering the trigger that is currently being created after the triggering 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 shown in: Row triggers require that when a DML statement moves multiple rows of data in a database, the trigger is activated once for each row of data, as long as they conform to the trigger constraint, and the statement trigger takes the entire statement action as the triggering event, and when it meets the constraint, Activates a trigger once. When the For each row option is omitted, the before and after triggers are statement triggers, and the instead OF triggers are only row triggers.

The referencing clause describes the correlation name, which can be used in the Pl/sql block and the When clause of the row trigger to reference the current new and old column values, with the default related names old and new. When you apply a correlation name to 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 trigger constraint. When Condition is a logical expression, it must contain the relevant name, not the query statement, or call the Pl/sql function. The trigger constraint specified by the When clause can only be used in before and after-row triggers and cannot be used 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 according to the base table changes it relies on, so it is not relevant to the application, and database triggers are used to guarantee the consistency and integrity of the data.

 

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

Before INSERT

Before INSERT for each ROW

After INSERT

After inserts for each ROW

Before UPDATE

Before UPDATE for each ROW

After UPDATE

After UPDATE for each ROW

Before DELETE

Before DELETE for each ROW

After DELETE

After the DELETE for each ROW

8.2.1 Trigger Trigger order

1. Execute before statement level triggers;

2. For each row that is affected by the statement:

L Execute before row-level triggers

L Execute DML statements