Talking about using triggers in SQL Server database

This article explains the use of triggers, which is important to understand the inserted process. 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.

The trigger is a reusable tool in database application, it is widely used. These days to write a chemical data statistics software, need to be based on sampling, automatic calculation of variance, here, I used the trigger. 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.

USE Northwind
CREATE TRIGGER OrdDet_Insert
ON [Order Details]
FOR INSERT
AS
UPDATE P SET
UnitsInStock = P.UnitsInStock – I.Quantity
FROM Products AS P INNER JOIN Inserted AS I
ON P.ProductID = I.ProductID

The work process of the Delete trigger