"SQL2000 trigger usage" Article Study Notes

Transfer from seagull track http://sgsoft.cnblogs.com
Trigger is a reusable tool in database applications. It is widely used. In the past few days, a software for chemical data statistics needs to be written. Based on sampling, the variance is automatically calculated. Here, I used a trigger.
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.

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:

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 do not have the same rows.

L when the deleted table is created, the space is allocated from the 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 record the truncate TABLE statement.

Reading Notes: truncate table is equivalent to a delete statement without where

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.

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.

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

Reading Notes: raiserror
When executing the (execute) stored procedure, the generated return code only notifies the user of the SQL server error. You can use the raiserror statement to notify users of errors that are not handled by SQL Server. These statements can be used to return user-defined error messages.

The following example checks whether a user-defined error occurs. The following code shows the numbers listed in descending order starting from the square of 5. When the number reaches 2, an error occurs and the loop is terminated.

Declare @ v_ctr int
Select @ v_ctr = 5
While @ v_ctr> 0
Begin
Select @ v_ctr * @ v_ctr
Select @ v_ctr = @ v_ctr-1
If @ v_ctr = 2
Raiserror ('counter has fallen below 3', 1, 2)
Break
End
End

Syntax
Raiserror ({msg_id | msg_str }{, severity, State}
[, Argument [,... n])
[With option [,... n]

Syntax analysis:
{Msg_id | msg_str}: the error message ID or text must be specified,
Severity: Specifies the error level (users can use the severity level from 0 to 18 ),
State: information about the error call status (the default value is 1 ).

Raiserror ('the level for job_id: % d shocould be between % d and % d .',
16, 1, @ job_id, @ min_lvl, @ max_lvl)
Or
Raiserror (50005, 16, 1, @ job_id, @ min_lvl, @ max_lvl)

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.

Reading Notes: Checkpoint
Forcibly write all dirty pages of the current database to the disk. Dirty pages refer to the data or log pages modified after being input to the cache, but these changes have not been written to the disk.

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:

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:

Create view customersview

Select * From customersger

Union

Select * From customersmex

Go

 

Create an instead of trigger in the preceding view:

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:

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'