Oracle Flip-Flop explanation

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 stored procedures and functions require user-displayed calls to execute, and triggers are initiated by an event

Run. 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). Oracle events are referred to as

Insert, update, and delete operations on tables in the database, or similar operations on the 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.DML Trigger

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

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

4. The trigger consists of:

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

4.2 Trigger Time: 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.

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

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

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

4.6 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 triggering event occurs, and a row-level trigger: The trigger is executed once for each row of data affected by the action when a triggering event occurs. When writing triggers, you need to be aware of the following: triggers do 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 the trigger, and you cannot use the DDL language (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 and old values of the same 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.

The general syntax for creating trigger creation 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;

Where: Before and after indicate that the trigger's trigger timing is pre-and post-trigger, respectively, before triggering events trigger the trigger that is currently created before the trigger event is triggered, 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 is manipulated to affect 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.

You can create up to 12 types of triggers per table, which are:

Before INSERT

Before INSERT for each ROW

After INSERT

After INSERT 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 DELETE for each ROW

Trigger Trigger Order

Executes before statement-level triggers; For each row affected by the statement:

Executing before row-level triggers

Executing DML statements

Executing after-row-level triggers

Executing after statement-level triggers

Example 1: Set up a trigger, when the Staff table EMP table is deleted a record, the deleted records are written to the Staff table delete log table.

CREATE TABLE Emp_his as SELECT * from EMP WHERE 1=2;

CREATE OR REPLACE TRIGGER tr_del_emp

Before Delete--Specifies that the trigger time is triggered before the delete operation

On Scott.emp

The For each row--description creates a row-level trigger

BEGIN

--Insert the pre-modified data into the Log record table del_emp for monitoring use.

INSERT into Emp_his (Deptno, empno, ename, Job, Mgr, Sal, Comm, HireDate)

VALUES (: Old.deptno,: Old.empno,: Old.ename,: Old.job,:old.mgr,: old.sal,: Old.comm,: old.hiredate);

END;

DELETE emp WHERE empno=7788;

DROP TABLE Emp_his; DROP TRIGGER del_emp;