Database objects--triggers--easy to go into the database series six __ Database

Triggers look at me a smattering of, see a trigger blog of a brother is particularly good, after reading, I feel that the boring gourd finally enlightened. First share this sharp good article, and then My personal supplement and mind map.

Share:

Introduction to Triggers :

Triggers (trigger) is a special kind of stored procedure, its execution is not by the program call, also does not need manual operation, it is by the event to trigger, the event everybody should be very familiar with, for instance the button click event, the webpage load event and so on. The Click event of the button is triggered by a mouse clicking button, and the event of the trigger is triggered by an incremental deletion of the table. Triggers are activated when additions or deletions to a database or table are modified (insert,delete,update).

Starting with SQL2005, the triggers are divided into two classes based on the different SQL statements, one is DML triggers and the other is DLL triggers. DML triggers fall into two categories: after triggers and instead OF triggers.

types 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 fall into two categories: after triggers and instead OF triggers

DDL triggers: DDL triggers are stored procedures that are executed when responding to data definition language (Language) events. DDL triggers are typically used to perform administrative tasks in a database. such as auditing and standardizing database operation, preventing database table structure from being modified, etc.

DML triggers:

Today we mainly introduce DML triggers, and DML triggers are divided into after triggers and instead OF triggers.

After triggers: Such triggers are activated after the record has been changed (after), which is mainly used to record the processing or inspection after the change, and can be rolled back and forth with the ROLLBACK TRANSACTION statement once the error is found.

Instead of triggers: This type of trigger is typically used to replace the original operation, which occurs before the change is recorded, and does not perform the operation in the original SQL statement (Insert, Update, Delete), and executes the action defined by the trigger itself.

In SQL Server, each DML trigger is assigned two special tables, one inserted table and one deleted table. Both exist in the memory of the database server, the logical table managed by the system, and the two temporary tables, not the physical tables that are actually stored in the database. The user has read-only permissions on both tables, and no modified permissions.

The structure of the two tables (the primary foreign key, field, data type, and so on) is exactly the same as the structure of the data table in which the trigger is located, and the two tables will be removed from memory when the trigger is finished.

The role of inserted and deleted two tables:

Inserted: For the Insert record operation, the Insert table holds the data to be inserted, and for the update record operation, the Insert table holds the record to be updated.

Deleted: For the update record operation, the delete table holds the updated record, and for the delete record operation, the deleted table is deposited with the deleted old record.

Look at the above two words may not quite understand, then look at the following table is not clear.

working principle:

After the trigger works:

After triggers are activated after the execution of the SQL statement. To delete a record as an example: When SQL Server receives an SQL statement for a delete operation, SQL Server stores the deleted records in the deleted table, deletes the records in the data table, activates the after trigger, and executes the SQL statement in the after trigger. After execution, delete the deleted table in memory, and the operation is complete.

Or the above example: in the product inventory, if you want to delete a product record, when deleted, the trigger can check whether the product inventory quantity is zero, if not zero cancel the deletion operation. The operation of the database is as follows:

1, receive the SQL statement, will be removed from the product inventory list of product records, put in the delete table.

2, delete the product record from the product inventory list.

3, from the deletion table read out the Product Inventory quantity field, judge is not zero, if zero, complete the operation, from the memory to clear delete tables, if not zero, with the ROLLBACK TRANSACTION statement to roll back operation (the inventory table revert to the state before the deletion).

How the Instead of triggers work :

Instead of triggers are different from after triggers. After triggers are activated after the INSERT, update, and delete operations are complete, the instead OF triggers are activated before they are done, and no longer execute the original SQL operation, but instead use the SQL statements of the trigger itself in place of the original statement.

And take the example above, when you delete a product record, use instead to replace the delete operation with the query for the product's inventory. The operation of the database is as follows:

1, receives the SQL statement, but does not execute, but jumps to the SQL statement after instead of

2, according to the incoming Product ID, the product inventory out, complete the operation.

Syntax:

reate TRIGGER <Trigger_Name>

On < table_name>

After|instead of < Insert,delete,update>

As

BEGIN

SQL statement

End

Go

Precautions:

1,after triggers can only be used for data tables that cannot be used for views, Instead of triggers, and views set to with CHECK option do not allow Instead of triggers to be established. Neither of these triggers can be built on a temporary table.

2, a data table can have multiple triggers, but a trigger can only correspond to one table.

3, in the same datasheet, many after triggers can be established for each operation, such as INSERT, Update, Delete, and the instead of trigger only establishes one for each operation.

4, if the after trigger is set for an operation and the instead of trigger is set, then the instead of trigger is bound to be activated, and after triggers are not necessarily activated.

5, different SQL statements can trigger the same trigger, such as INSERT and UPDATE statements, which can activate the same trigger.

6, the trigger name must be unique in the database in which it resides. Because triggers are built in data tables or views, many people assume that the names of triggers can be the same as long as they are in a different datasheet. In fact, the full name of the trigger (Server.Database.Owner.TriggerName) must be unique, regardless of which datasheet or view the trigger is in.

7, the keyword after can be used for to substitute, they mean all the same, represents the trigger that is activated only after the operation of the datasheet has been completed correctly.

Personal Understanding:

Add one point: After the trigger is to check the event after the confirmation ,

And instead of is the substitution operation before the event occurs.

DDL triggers are also grouped into two categories: database level and server level.

Attach a mind map to help Digest.