Use of triggers in SQL Server 2000

Below I have excerpted a text section about the trigger in the SQL Server official tutorial, which is a useful text description.
You can define a trigger that will be executed whenever the INSERT statement is used to INSERT data into the table.
When an INSERT trigger is triggered, new data rows are inserted into the trigger table and inserted Table. An inserted Table is a logical table that contains a copy of the inserted data rows. The inserted table contains the INSERT actions recorded in the INSERT statement. The inserted Table can also reference the log data generated by the initialization of the INSERT statement. The trigger checks the inserted Table to determine whether to execute the trigger action or how to execute it. Rows in the inserted Table are always copies of one or more rows in the trigger table.
The log records all data modification actions (INSERT, UPDATE, and DELETE statements), but the information in the transaction log is unreadable. However, the inserted Table allows you to reference the Log changes caused by the INSERT statement, so that you can compare the inserted data with the changes to verify them or take further actions. You can also directly reference the inserted data without storing them in variables.
Example
In this example, a trigger is created. This trigger updates a column (UnitsInStock) in the Products Table whenever a product is ordered (insert a record to the Order Details table at any time ). The original value minus the purchased quantity is the new value.
Copy codeThe Code is as follows:
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

Working 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 deleted data row. The deleted table can also reference log data generated by the initialization DELETE statement.
When using the DELETE trigger, consider the following items and principles:
· 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 do not have the same rows.
· When the deleted table is created, the space is allocated from the memory. The deleted table is 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 example, a trigger is created to update 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 marked as 1, indicating that these products are no longer used.
Copy codeThe Code is as follows:
USE Northwind
Create trigger Category_Delete
ON Categories
FOR DELETE
AS
Update p set Discontinued = 1
FROM Products as p inner join deleted AS d
On p. CategoryID = d. CategoryID

UPDATE trigger procedure
The UPDATE statement can be regarded as a two-step operation: the DELETE statement that captures the before image and the INSERT statement that captures the after image. When an UPDATE statement is executed on a table with a trigger defined, the original row (front image) is moved to the deleted table, and the updated row (Back image) is moved to the inserted Table.
The trigger checks the deleted table, inserted Table, and updated table to determine whether multiple rows are updated and how to execute the trigger action.
You can use the if update statement to define a trigger to monitor data updates of a specified column. In this way, the trigger can easily isolate the activity of a specific column. When it detects that the specified column has been updated, the trigger will further execute the appropriate action. For example, an error message indicates that the column cannot be updated, or execute a series of action statements based on the new updated column value.
Syntax
If update (<column_name>)
Example 1
In this example, the user is prevented from modifying the employee ID column in The Employees table.
Copy codeThe Code is 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

Working process of the instead trigger
You can specify the instead of trigger on a table or view. Executing this trigger can replace the original trigger action. The instead of trigger extends the type OF view update. For each trigger action (INSERT, UPDATE, or DELETE), each table or view can have only one INSTEAD trigger.
The INSTEAD trigger is used to update views that cannot be updated normally. For example, you cannot DELETE a connection-based view. However, you can write an instead of delete trigger to DELETE the object. The above trigger can access the data rows that have been deleted when the view is a real table. Store the deleted rows in a worksheet named 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 trigger in a view WITH the 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 will redirect the update operation to the appropriate base table. The insert operation is performed on the CustomersGer table instead of the view.
Create two tables that contain customer data:
Copy codeThe Code is as follows:
SELECT * INTO mermersger FROM Customers WHERE Customers. Country = 'Germany'
SELECT * INTO mermersmex FROM Customers WHERE MERs. Country = 'mico'
GO

Create a view on the data:
Copy codeThe Code is as follows:
Create view CustomersView
SELECT * FROM CustomersGer
UNION
SELECT * FROM CustomersMex
GO

Create an instead of trigger in the preceding view:
Copy codeThe Code is as follows:
Create trigger Customers_Update2
ON CustomersView
INSTEAD OF UPDATE
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 = 'docker'
BEGIN
UPDATE CustomersMex
SET CustomersMex. Phone = Inserted. Phone
FROM CustomersMex JOIN Inserted
ON CustomersMex. CustomerID = Inserted. CustomerID
END

Test the trigger by updating the View:
Copy codeThe Code is 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'

How can we calculate the variance for multiple columns of data? :
Copy codeThe Code is as follows:
Create trigger [calT1T2T3] ON dbo. DCLB
For insert, UPDATE
AS
Update P
SET
/**//*
Trigger for Variance Calculation
*/
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

Triggers are easy to use and easy to use. It is important to understand the inserted process. The UPDATE statement can be regarded as a two-step operation: the DELETE statement that captures the before image and the INSERT statement that captures the after image. When an UPDATE statement is executed on a table with a trigger defined, the original row (front image) is moved to the deleted table, and the updated row (Back image) is moved to the inserted Table. The trigger checks the deleted table, inserted Table, and updated table to determine whether multiple rows are updated and how to execute the trigger action.