Here's an excerpt of a text description of the trigger in the official SQL Server tutorial, which is really useful.
You can define a trigger that executes whenever you insert data into a table with an INSERT statement.
When the insert trigger is triggered, the new data row is inserted into the trigger table and the inserted table. The inserted table is a logical table that contains a copy of the data rows that have been inserted. The inserted table contains the inserted actions that have been recorded in the INSERT statement. The inserted table also allows you to reference the log data generated by initializing the INSERT statement. The trigger checks the inserted table to determine whether to execute the trigger action or how to execute it. A row in a inserted table is always a copy of one or more rows in the trigger table.
The log records all the actions that modify the data (INSERT, update, and DELETE statements), but the information in the transaction log is not readable. However, the inserted table allows you to reference the log changes caused by the INSERT statement, so that you can compare the insertion data with the changes that occurred to verify them or take further action. You can also reference the inserted data directly, without having to store them in a variable.
Example
In this case, a trigger is created. Whenever a product is ordered (whenever a record is inserted into the Order Details table), the trigger updates a column (UnitsInStock) in the Products table. Subtracting the ordered quantity value with the original value is the new value.
Copy Code code as follows:
Use Northwind
CREATE TRIGGER Orddet_insert
On [Order Details]
For INSERT
As
UPDATE P SET
UnitsInStock = p.unitsinstock–i.quantity
From the products as P INNER JOIN Inserted as I
On p.productid = I.productid
The work process of the Delete trigger
When the delete trigger is triggered, rows deleted from the affected table are placed in a special deleted table. The deleted table is a logical table that retains a copy of the data rows that have been deleted. The deleted table also allows reference to the log data generated by the initialization DELETE statement.
When you use the Delete trigger, you need to consider the following issues and guidelines:
• When a row is added to the deleted table, it no longer exists in the database table; Therefore, deleted tables and database tables do not have the same rows.
• When creating a deleted table, space is allocated from memory. Deleted tables are always stored in the cache.
• The trigger defined for the delete action does not execute the TRUNCATE TABLE statement because the log does not record the TRUNCATE TABLE statement.
Example
In this case, a trigger is created that updates the Discontinued column in the Products table whenever a product category is deleted (that is, a record is deleted from the Categories table). All affected products are labeled 1 and are no longer used for these products.
Copy Code code as follows:
Use Northwind
CREATE TRIGGER Category_delete
On Categories
For DELETE
As
UPDATE P SET discontinued = 1
From the products as P INNER JOIN deleted as D
On P.categoryid = D.categoryid
The work process of the update trigger
You can consider an UPDATE statement as a two-step operation: A DELETE statement that captures the image before the data (before image), and an INSERT statement that captures the data (after image). When an UPDATE statement is executed on a table that has a trigger defined, the original row (pre-image) is moved into the deleted table, and the update row (after image) is moved into the inserted table.
Triggers check deleted tables and inserted tables and updated tables to determine whether multiple rows have been updated and how trigger actions are executed.
You can use the IF UPDATE statement to define a trigger that monitors the data update for a specified column. This allows triggers to easily isolate specific columns of activity. When it detects that the specified column has been updated, the trigger further performs the appropriate action, such as sending an error message stating that the column cannot be updated, or executing a series of action statements based on the new updated column values.
Grammar
IF UPDATE (<column_name>)
Example 1
This example prevents the user from modifying the EmployeeID column in the Employees table.
Copy Code code as follows:
Use Northwind
Go
CREATE TRIGGER Employee_update
On Employees
For UPDATE
As
IF UPDATE (EmployeeID)
BEGIN
RAISERROR (' Transaction cannot be processed.\
Employee ID number cannot be modified. ', 10, 1
ROLLBACK TRANSACTION
End
INSTEAD The working procedure of a trigger
You can specify the instead of triggers on a table or view. Executing this trigger can replace the original triggering action. The INSTEAD of triggers extend the type of the view update. For each trigger action (INSERT, update, or DELETE), each table or view can have only one instead of trigger.
INSTEAD of triggers are used to update views that do not have the means to update in a normal way. For example, you typically do not have a delete operation on a connection-based view. However, you can write a instead of DELETE trigger to implement the deletion. The above triggers can access data rows that have been deleted if the view is a real table. Store the deleted rows in a worksheet named deleted, just like after triggers. Similarly, in the update INSTEAD of triggers or insert INSTEAD of triggers, you can access new rows in the inserted table.
You cannot create a instead of trigger in a view with the WITH CHECK option definition.
Example
In this example, a German customer table and a Mexican customer table are created. The instead of trigger placed on the view redirects the update operation to the appropriate base table. The insertion that occurs at this point is the insertion of the Customersger table instead of the view.
Create two tables that contain customer data:
Copy Code code as follows:
SELECT * into Customersger from Customers WHERE customers.country = ' Germany '
SELECT * into Customersmex from Customers WHERE customers.country = ' Mexico '
Go
To create a view on this data:
Copy Code code as follows:
CREATE VIEW Customersview as
SELECT * from Customersger
UNION
SELECT * from Customersmex
Go
Create a instead of trigger on the above view:
Copy Code code as follows:
CREATE TRIGGER Customers_update2
On Customersview
INSTEAD of UPDATE as
DECLARE @Country nvarchar (15)
SET @Country = (SELECT Country from Inserted)
IF @Country = ' Germany '
BEGIN
UPDATE Customersger
SET Customersger.phone = Inserted.phone
From Customersger JOIN Inserted
On customersger.customerid = Inserted.customerid
End
ELSE
IF @Country = ' Mexico '
BEGIN
UPDATE Customersmex
SET Customersmex.phone = Inserted.phone
From Customersmex JOIN Inserted
On customersmex.customerid = Inserted.customerid
End
To test the trigger by updating the view:
Copy Code code as follows:
UPDATE customersview SET Phone = ' 030-007xxxx '
WHERE CustomerID = ' ALFKI '
SELECT CustomerID, Phone from Customersview
WHERE CustomerID = ' ALFKI '
SELECT CustomerID, Phone from Customersger
WHERE CustomerID = ' ALFKI '
So specifically, how do you compute the variance for multiple columns of data? ：
Copy Code code as follows:
CREATE TRIGGER [calt1t2t3] on dbo. Dclb
For Insert,update
As
Update P
SET
/**//*
Triggers that compute variance
*/
P.t1= (I.P1+I.P2+I.P3+I.P4+I.P5+I.P6),
P.t2= (I.Y1+I.Y2+I.Y3+I.Y4+I.Y5+I.Y6),
P.T3=SQRT (P.T1*P.T1+P.T2*P.T2)
From DCLB as P INNER JOIN Inserted as I
On p.sid = I.sid
The use of triggers is very convenient, but also very simple, it is important to understand the inserted process. You can consider an UPDATE statement as a two-step operation: A DELETE statement that captures the image before the data (before image), and an INSERT statement that captures the data (after image). When an UPDATE statement is executed on a table that has a trigger defined, the original row (pre-image) is moved into the deleted table, and the update row (after image) is moved into the inserted table. Triggers check deleted tables and inserted tables and updated tables to determine whether multiple rows have been updated and how trigger actions are executed.
