The trigger is a reuse tool in the database application. Its application is very wide. These days written a chemical statistics software, which needs to be calculated according to sampling, automatic calculation, here, I use triggers. Below I extract a word about the trigger in the official tutorial of the SQL Server, a single text description of the trigger. _________________________________________________________
You can define a trigger that will execute whenever you use an Insert statement to insert data into a table.
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 logic table that contains a copy of the data row that has been inserted. The INSERTED table contains the recorded insertion action in the INSERT statement. The INSERTED table also allows reference to reference to log data generated by initialization INSERT statements. The trigger determines if the trigger action is executed by checking the INSERTED table or how to perform it. The rows in the Inserted table are always a one or more copies of the trigger table.
The log records all the motion of modifying the data (INSERT, UPDATE, and DELETE statements), but information in the transaction log is unreadable. However, the INSERTED table allows you to reference log changes caused by the INSERT statement so that the inserted data can be compared to the changes to verify that they or take a further action. You can also directly reference the inserted data without having to store them into the variable.
Example
In this example, a trigger will be created. This trigger will update a column in the Products table. Using the original value to reduce the amount of the order is a new value.
Use northwind
Create Trigger ORDET_INSERT
ON [ORDER DETAILS]
For insert
AS
Update p set
UnitsInstock = P.UnitsInstock - i.quantity
From Products as P inner Join Insert As I
On P.ProductId = I.ProductID
DELETE trigger work process
When the DELETE trigger is triggered, the rows deleted from the affected table will be placed in a special deleted table. The deleted table is a logical table that retains a copy of the deleted data line. The DELETED table also allows references to reference log data generated by initializing the delete statement.
When using the DELETE trigger, you need to consider the following matters and principles:
l When a row is added to the deleted table, it no longer exists in the database table; therefore, the deleted table and the database table have no different lines.
l When you create a DELETED table, the space is allocated from memory. The deleted table is always stored in the cache.
l The trigger defined for the delete action does not execute the TRUNCATE TABLE statement because the log does not log in the TRUNCATE TABLE statement.
Example
In this example, a trigger will be created, and a product category is created, which will update the discucts table in the Products table whenever a product category is removed (ie, deleting a record from the Categories table). All affected products are marked as 1, and these products are not used.
Use northwind
Create Trigger Category_Delete
On Categories
For delete
AS
Update p set discontinued = 1
From products as p.categoryId = D.categoryId
UPDATE trigger work process
You can view the UPDATE statement to two steps: that is, the DELETE statement that captures the Before Image, and the Insert statement of the AFTER Image. When the UPDATE statement is executed on the table defined on the trigger, the original row (front image) is moved to the deleted table, and the update line (post-image) is moved to the INSERTED table.
The trigger checks the DELETED table and the Inserted table and the updated table to determine if the multi-line and how to perform trigger actions.
You can use the if Update statement to define a trigger that monitors the data update of the specified column. This allows the trigger to easily isolate a particular column activity. When it detects that the specified column has been updated, the flip-flop will further perform the appropriate action, such as issuing an error message indicating that the column cannot be updated, or perform a series of action statements based on the new updated column value.
grammar
IF update (
example 1
This example prevents the user from modifying the EMPLOYEEID column in the Employees table.
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 OF Trigger work process
The INSTEAD OF trip can be specified on the table or view. Executing this trigger can replace the original trigger. The INSTEAD OF trip extends the type of view update. For each trigger (INSERT, UPDATE, or DELETE), each table or view can only have an INSTEAD OF trip.
The INSTEAD OF trip is used to update the views that have no way to update through normal mode. For example, it is usually not possible to perform a DELETE operation on a connection-based view. However, an Instead of Delete trigger can be written to implement deletion. The above trigger can access the data rows that have been deleted if the view is a true question. The deleted row is stored in a worksheet called deleted, just like the After trigger. Similarly, in the Update Instead Of trigger or Insert Instead Of trigger, you can access the new row in the inserted table.
You cannot create an INSTEAD OF trip in a view with the WITH CHECK OPTION.
Example
In this example, a German client table and a Mexican customer table are created. The instead of trigger placed on the view will redirect the update operation to the appropriate base table. The insert that occurred at this time is insertion of the Customersger table instead of the insertion of the view.
Create two tables that contain customer data:
Select * INTO CUSTOMERSGER from Customers Where Customers.country = '
Germany '
Select * INTO CUSTOMERSMEX from Customers Where Customers.country = '
MEXICO '
Go
Create a view on this data:
Create View
CustomersView
AS
Select * from customersger
Union
Select * from Customersmex
Go
Create an instead of trigger on the above view:
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 customer.phone = inserted.phone
From customersger join inserted
On Customersger.customerid = Insert.customerid
End
Else
IF @country = '
MEXICO '
Begin
Update Customersmex
Set customer = insert.phone
From customersmex join inserted
ON Customersmex.customerid = Insert.customerid
End
Test the trigger by updating the view:
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'
------------------------------------------ then then specifically, How to calculate the difference between multiple columns? :
Create
Trigger
[
Calt1t2t3
]
On
DBO.DCLB
For
Insert
,
Update
AS
Update
P
Set
/ ** /
/ * Trigger of calculation of 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
Insert
AS
I
On
P.SID
=
I.SID