Triggers in SQL 2000 use

xiaoxiao2021-03-05  24

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

转载请注明原文地址:https://www.9cbs.com/read-38185.html

New Post(0)