After trigger of SQLSERVERDML trigger

Depending on the time the DML trigger occurs and the language used to write the trigger, it can be divided into after triggers, INSTEAD of triggers, and CLR triggers. After triggers are fired after an INSERT, update, or DELETE statement operation, INSTEAD of triggers and constraints. INSTEAD is fired before the constraint is processed, so you can use other statements in INSTEAD of to replace the INSERT, update, and so on that fired the trigger. Also, you can define instead of triggers for views based on one or more base tables, thereby extending the types of updates that the view can support. CLR triggers can be after triggers or instead OF triggers, and can also be DDL triggers.

It should be noted that when you create a DML trigger, you cannot use the following statement:

ALTER database CREATE Database DROP database

Load DATABASE load LOG reconfigure

Restore DATABASE Restore LOG

14.1.1 after triggers

A table can have multiple after triggers, as long as their names are different. Each trigger can be applied to only one table, but a trigger may apply to three user actions (UPDATE, insert, and delete) for one table at a time.

The following statement creates a Pritrigger table and a detailtable in which the Pritrigger table holds the number and amount of the sales order, detailtable the table to hold the product information in each order. A trigger named Pritrigger was created for the delete operation of the Pritrigger table, and when the order information in the Pritrigger table is deleted, the trigger deletes the product information for the order in the Detailtable table.

Use AdventureWorks;

Go

--Create the main table, store the sales order number and amount

CREATE TABLE pritable

(OrderID int IDENTITY (1,1), OrderTotal money);

Go

--Create a schedule that holds the product information from each order

CREATE TABLE detailtable

(OrderID int, ProductID int, ProductCount int not NULL, price money);

Go

--Insert order information into the primary table

INSERT into Pritable VALUES (2100.00);

INSERT into Pritable VALUES (1000.00);

--Product information for inserting orders into the schedule

INSERT into Detailtable VALUES (1,1,10,110.00);

INSERT into Detailtable VALUES (1,2,10,100.00);

INSERT into Detailtable VALUES (2,2,10,100.00);

Go

--Create a trigger for the Pritrigger table

CREATE TRIGGER Pritrigger

On pritable

After DELETE

As

DELETE from Detailtable

WHERE OrderID in (SELECT OrderID

from Deleted);

PRINT N ' has deleted the relevant data from the Detailtable table '--this sentence is only necessary for demonstration purposes, and should not be used in triggers

When a trigger is defined, the trigger name is after the CREATE TRIGGER keyword, and the ON clause specifies the base table on which to create the trigger. After clauses (you can also use for to replace the After keyword, both of which function the same) Specify the action statement that activates the trigger, and you can specify multiple action statements at the same time. For example, "after delete, insert" means activating a trigger when a delete, insert statement is executed against a table. Specify what actions the trigger performs after the AS keyword.

Note the deleted keyword in the IN clause in the Where condition. When rows are deleted from the Pritrigger table, the deleted rows are copied to a temporary memory table named deleted. If a trigger is specified for a table when an INSERT statement is executed, the new row is added to a temporary memory table named inserted when the row is inserted into the table. If a trigger is specified for the table when an UPDATE statement is executed, the update transaction is similar to performing the insert operation after the delete operation. As a result, the old rows are copied into the deleted table, and the new rows are copied to the trigger table and the inserted table.

Deleted tables and inserted tables are created and managed automatically by SQL Server, with the same structure as the base table that defines the trigger.

Execute the following statement to delete the row OrderID 1 from the Pritable table, when the trigger automatically deletes the related rows in the Detailtable table, and the resulting results and messages are shown in Figure 14-1.

DELETE from pritable WHERE OrderID = 1;

SELECT * from Pritable;

SELECT * from Detailtable;

Figure 14-1 Deleting the results and messages from rows OrderID to 1 in the Pritable table

If you execute the following statement, you are ready to delete the row OrderID 3 from the Pritable table. Because such a row does not exist in the pritable table, it does not delete the success. Although no deletion succeeded, you can still see the information sent back by the trigger's print statement in the message window. This means that the trigger is activated even if the statement does not affect the rows in the table. In the absence of a successful deletion, the deleted table is an empty table.

DELETE from pritable WHERE OrderID = 3;