Creation and considerations of triggers in a database

the "It expert network exclusive" trigger is a special stored procedure, but its execution is not performed by hand or by the program, and triggers must be triggered by events. Trigger events can be divided into 3 classes, DML events, DDL events, and database events, respectively.

　　Highlights of DML triggers

A DML trigger is a trigger that is defined on a table and raised by a DML event. The features that write DML triggers are:

Determines the triggered table, which is the table on which the trigger is defined.

To determine which event is triggered, the trigger event for the DML trigger is INSERT, UPDATE, and delete three

Determines the trigger time. The triggering time is before and after two, respectively, indicating that the triggering action occurs before the DML statement executes and after the statement executes.

To determine the trigger level, there are both statement-level triggers and row-level triggers. A statement-level trigger indicates that the SQL statement triggers only one trigger at a time, and a row-level trigger indicates that each row affected by the SQL statement is fired once.

Because multiple DML triggers can be defined on the same table, the trigger itself and the SQL statement that raises the trigger have a sequential relationship in the order in which they are executed. The order of them is:

If there is a statement-level before trigger, the statement-level before trigger is executed first.

In the execution of an SQL statement, if there is a row-level before trigger, the SQL statement executes the row-level before trigger before the row is executed before each row is manipulated. If there is a row-level after trigger, the SQL statement executes the row-level after trigger again after each row operation.

If there is a statement-level after trigger, the last statement-level after trigger is executed once the SQL statement finishes executing.

DML triggers also have some specific issues, as explained below:

If more than one trigger is defined to be the same time, the same event is triggered, and the last defined trigger is valid, the last defined trigger is triggered and the other triggers are not executed.

A trigger can be triggered by a number of different DML operations. In triggers, you can use inserting, DELETING, and updating predicates to differentiate between different DML operations. These predicates can be used as a judging condition in an IF branch conditional statement.

In row-level triggers, the values before and after data changes are accessed using: New and: old (called Pseudo-record). Note, however, that the INSERT statement inserts a new record, so there is no: old record, and the DELETE statement deletes a record that already exists, so there is no: new record. The UPDATE statement includes both the old record and the new record, which represents the record before and after the change. The method that references the value of a specific column is:

: Old. Field name or: New field name

Commit, ROLLBACK, savepoint statements are forbidden in the trigger body, and stored procedures containing the above statements are not directly or indirectly invoked.

Consider a number of scenarios when defining a trigger, and determine the type of trigger based on your specific needs.

Creating a DML trigger requires the CREATE TRIGGER system permission. The syntax for creating a DML trigger is as follows:

CREATE OR REPLACE TRIGGER tg_name

Before | After

INSERT or DELETE or UPDATE [of Col1,col2,...]

On table name

[For each ROW]

[When condition]

DECLARE

...

BEGIN

....

EXCEPTION

...

END;

Note: A) for each row represents A row-level trigger.

B) When: Trigger only if the condition is met.

C) Insert,delete,update operation can be identified by inserting,deleting,updating.

D) Use: New and: old (called Pseudo record) to access values before and after the data change.

: The data after new:insert,update.

: Old:D data prior to elete,udpate.

: Old. Field name or: New field name

E) The action in the trigger and the trigger event (Insert,update,delete) are both commit and rollback;

REM creates a trigger dml_log that includes inserting, deleting, and modifying various triggering events, recording the operation of the EMP table.

Creation and considerations of triggers in a database