Triggers in SQL 2000 use

Trigger
The trigger is the database application Reuse tool, its application is very extensive, these days writes a chemical data statistics aspect software, needs according to the sampling, calculates the variance automatically, here, I use the trigger. 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.







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:







L 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.







L The space is allocated from memory when the deleted table is created. Deleted tables are 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 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.







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.







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:







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:







CREATE VIEW

Customersview as







SELECT * from Customersger









UNION







SELECT * from Customersmex







Go















Create a 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 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:







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? ：
CREATE TRIGGER [calt1t2t3] on dbo. DCLB for insert,updateasupdate pset/**//* the trigger */p.t1= (I.P1+I.P2+I.P3+I.P4+I.P5+I.P6) that calculates variance, 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 ION = P.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.