Shulou(Shulou.com)06/01 Report--

The concept of triggers:

It is a kind of special event-driven process defined by the user on the relational table. Once defined, any addition, deletion and modification of the table will have the corresponding trigger automatically activated by the server to carry out centralized integrity control in the DBMS core layer. Similar to constraints, but more flexible than constraints.

Classification of triggers:

DML triggers: DML (Data Manipulation Language) triggers are stored procedures that are executed when a data manipulation language event occurs in the database server. DML triggers are divided into two categories: After triggers and Instead Of triggers.

DDL triggers: DDL triggers are stored procedures that execute in response to data definition language (Data Definition Language) events. DDL triggers are generally used to perform administrative tasks in the database. Such as auditing and standardizing database operation, preventing database table structure from being modified, and so on.

After trigger: this kind of trigger will not be activated until the record has been changed (after). It is mainly used to record the processing or check after the change. Once an error is found, you can also use the Rollback Transaction statement to roll back the operation.

Instead Of trigger: this type of trigger is generally used to replace the original operation, which occurs before the change is recorded. It does not perform the operation in the original SQL statement (Insert, Update, Delete), but performs the operation defined by the trigger itself.

In SQL Server, each DML trigger is assigned two special tables, one is the Inserted table and the other is the Deleted table. The two of them exist in the memory of the database server, are logical tables managed by the system, and are two temporary tables, not physical tables that are actually stored in the database. The user only has read permission for these two tables and does not have permission to modify them.

The structure of these two tables (primary foreign key, field, data type, etc.) is exactly the same as the structure of the data table in which the trigger is located, and when the work of the trigger is completed, the two tables will also be deleted from memory.

Define triggers:

Use the CREATE TRIGGER command to create a trigger in a general format:

CREATE TRIGCER | BEFORE | AFTER | ONFOR EACH | ROW | STATEMENT | {WHEN}

1. Trigger name: may or may not contain the pattern name, in the same mode, the trigger name must be unique; and the trigger name and

Must be in the same mode.

two。 Table name: the table data changes by activating the corresponding trigger defined on the table, also known as the target table of the trigger.

three. Trigger event: it can be INSERT, DELETE. Update, or a combination of these events. Indicate which columns are modified when the trigger is changed

A combination, which can be a combination of several events and can attach an OF.

4. Trigger type: row-level trigger (FOR EACH ROW), statement-level trigger (FOR EACH STATEMENT).

5. Trigger condition: when activated, the action is triggered only when the trigger condition is true. If WHEN is omitted, the trigger agent is touching.

Execute immediately after the trigger is activated

6. Touch initiator: an anonymous PL/SQL procedure block, or a call to a created stored procedure.

In the case of row-level triggers, the NEW/OLD reference, the new value after the UPDATE/INSER event and the old value before the UPDATE/DELETE event can be used in both cases. If it is a statement-level trigger, you cannot use a NEW/OLD reference in the trigger body.

Let's look at an example:

Define a BEFORE row-level trigger to define the integrity rules for the teacher's table Teacher: "the salary of a professor shall not be less than 4000 yuan. If it is lower, it will be automatically changed to 4000 yuan."

CREATE TRLGGER Insert_Or_Updata_Sal / * define trigger on faculty table Teacher * / BEFORE INSERT OR UPDATE ON Teacher FOR EACH ROW AS BEGIN IF (new.Job = 'professor') AND (new.Sal < 4000) THEN new.Sal: = 4000; END IF END

Define AFTER row-level trigger, and automatically add a corresponding record to the salary change table Sal_log when the salary of the teacher's table Teacher changes.

CREATE TRLGGER Insert_Sal AFTER INSERT ON Teacher FOR EACH ROW AS BEGIN INSERT INTO Sal_log VALUES (new.Eno,new.Sal,CURRENT_USER,CURRENT_TIMESTAMP); END; CREATE TRLGGER Update_Sal AFTER UPDATE ON Teacher FOR EACH ROW AS BEGIN IF (new.Sal old.Sal) THEN INSERT INTO Sal_log VALUES (new.Eno,new.Sal,CURRENT_USER,CURRENT_TIMESTAMP) END IF; END

Activate the trigger:

Execute BEFORE triggers on the table

Activate the SQL statement of the trigger

Execute the AFTER trigger on the table.

Delete trigger:

DROP TRIGGER ON

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.