SQL Server triggers

Document directory

• Introduction

-- Analysis trigger

1. About inserted tables and deleted tables

Two special tables are used in the trigger statement: inserted Table and deleted table.

The inserted table contains the data inserted in this insert operation or the data updated in this update operation.

The deleted table contains the deleted data in this delete operation or the data before the update operation.

2. Number of records in the inserted and deleted tables.

The number of records depends on whether it is batch insert or single insert.
For example:
Insert TB select 1 Union select 2 Union select 3 Union select 4
-- All of the above are batch inserts. Only one insert trigger is triggered, but there are four data records in inserted at the same time.
Insert TB select 1
Insert TB select 2
Insert TB select 3
Insert TB select 4
-- The above is a single insert. Each insert triggers an insert trigger, and each trigger only contains one data.

3. This is also important.

Use the statements in the trigger and the statements that trigger it as a single transaction that can be rolled back in the trigger.

That is to say, if the data is successfully inserted into the table, but the trigger operation reports an error, the entire transaction is rolled back, that is
Insert and rollback of Data in this table, and statement operation rollback in the trigger.
If the SQL statement in the trigger is rolled back without affecting the original operation, we recommend that you use
Set xact_abort off.

4. About the for trigger and instead of trigger.

For is equivalent to after, that is, the statement in the trigger is executed only after insert, delete, or update.
Instead of is equivalent to replacing insert, delete, or update operations. Although, at this time, inserted or deleted are two special
The table contains data but does not insert, delete, or update the data in the table.
To insert, delete, or update the table.

5、if update(colname)

Note that this will not only be used in the update trigger, but also in the insert trigger. The following is a description:

Test changes to specific columns
The IF update clause in the trigger definition can be used to determine whether the insert or update statement affects a specific column in the table.
This clause is true whenever a column is assigned a value.
Also:
If columns_updated () clause checks the columns that have been updated using the insert or update statement. This clause uses the integer bit mask to specify the columns to be tested.
Http://blog.csdn.net/sdhdy/archive/2009/06/07/4249668.aspx

Introduction triggers can query other tables and contain complex SQL statements. They are mainly used to force complex business rules or requirements. For example, you can control whether to insert a new order based on the current account status of the customer.
Triggers can also be used to force reference integrity so that the relationships defined between these tables are retained when rows are added, updated, or deleted in multiple tables. However, the best way to force reference integrity is to define the primary key and foreign key constraints in the relevant table. If you use a database relationship diagram, you can create a relationship between tables to automatically create a foreign key constraint. SQL syntax for creating a trigger delimiter |
Create trigger '<databasename>'. '<triggername>'
<[Before | after]> <[insert | update | delete]>
On <tablename>
For each row
Begin
-- Do something
End | advantages of a trigger
Triggers can implement cascade changes through relevant tables in the database. However, they can be executed more effectively through cascading integrity constraints. The trigger can force a check
Constraints defined by constraints are more complex. Unlike check constraints, triggers can reference columns in other tables. For example, a trigger can use the select
Compare the inserted or updated data and perform other operations, such as modifying data or displaying user-defined error messages. The trigger can also evaluate the table status before and after data modification and take countermeasures based on the difference. In a table
Multiple similar triggers (insert, update, or delete) allow different countermeasures to respond to the same modification statement. Comparison triggers and constraints
Constraints and triggers have their own advantages in special circumstances. The main benefit of triggers is that they can contain the use of transact-SQL
The complex processing logic of the Code. Therefore, a trigger can support all the functions of the constraint, but it is not always the best method for the given functions. Entity integrity should always be enforced by indexing at the lowest level.
An index is either part of the primary key and unique constraints, or is created independently of the constraints. Assume that the function can meet the functional requirements of the application, and the domain integrity should pass through
Check constraints are enforced, while the reference integrity (RI) should be passed through foreign key
Constraint. Triggers are extremely useful when the functions supported by constraints cannot meet the functional requirements of applications.
For example, unless the references clause defines a cascade reference operation, the foreign key constraint can only verify the column value by a value that exactly matches the value in the other column.
The check constraint can only verify the column value based on a logical expression or another column in the same table. If the application requires that the column value be verified based on the columns in another table, the trigger must be used.
Constraints can only pass error messages through standard system error messages. If the application requires (or can benefit from) custom information and more complex error handling, the trigger must be used.
Triggers can implement cascade changes through relevant tables in the database. However, they can be executed more effectively through cascading integrity constraints.
The trigger can disable or roll back the changes that violate the integrity of the reference to cancel the data modification. When the foreign key is changed and the new value is
If the key does not match, such triggers may take effect. For example, you can create an insert trigger on titleauthor. title_id
Roll back an insert when a value in titles. title_id does not match. However, foreign key is usually used for this purpose.
If there are constraints on the trigger table, check these constraints after the instead of trigger is executed but before the after trigger is executed. If the constraints are damaged, roll back the instead of trigger operation without executing the after trigger.
Whether a trigger can be created in a view in SQL Server books online does not mean that the trigger cannot be created in the view, and the syntax explanation indicates:
After the create trigger on, it can be a view.
However, this does not seem to be the case. Many experts also say that triggers cannot be created on The View. I have also made a special test. Indeed, neither a common view nor an index view can create a trigger on it.
Is that true? Click details, but it is understandable that a trigger is rejected when it is created in a temporary table or system table. A deep understanding of the for create trigger statement
The for keyword can be followed by one or more insert, update, and delete keywords, that is, the trigger will not be triggered in other cases, including
Select, truncate, writetext, updatetext. Related content
In an interesting application, we can see that many registration systems cannot change the user name after registration, but most of these are determined by the application. If you directly open the database table to make changes, you can also change the user name,
You can use rollback in a trigger to cleverly implement the inability to change the user name ...... Details: When an error occurs in the internal statement of the trigger ......
In this case, the previous data change operation will be invalid. For example, if a trigger is triggered when data is inserted in a table and a running error occurs in the trigger, an error value is returned and
. Most T-SQL statements cannot be used in statement triggers that are used in triggers, but the following statements cannot be used in triggers.
Create statement, such as create database, create table, and create index.
Alter statements, such as alter database, alter table, and alter index.
Drop statements, such as drop database, drop table, and drop index.
Disk statement, such as disk init and disk resize.
Load statement, such as load database and load log.
Restore statements, such as Restore database and restore log.
Reconfigure
The truncate TABLE statement is not available in Sybase triggers! The use of trigger triggers with caution is powerful, allowing you to easily and reliably implement many complex functions. Why should we use them with caution. Trigger itself is not at fault, but because of our misuse, it will cause difficulties in database and application maintenance. In database operations, we can perform data operations through relationships, triggers, stored procedures, and applications ...... Meanwhile, rules, constraints, and default values are also an important guarantee for data integrity. If we rely too much on the trigger, it will inevitably affect the database structure and increase the maintenance of complex programs.

SQL Server triggers
Http://blog.csdn.net/lavly/archive/2009/07/18/4356275.aspx

1. A trigger is a special stored procedure. It cannot be explicitly called. Instead, it is automatically activated when a record is inserted, updated, or deleted into the table. Therefore, triggers can be used to implement complex integrity constraints on tables.

2. SQL Server creates two special tables for each trigger: The inserted Table and the deleted table. These two tables are maintained by the system and exist in the memory instead of in the database. The structure of these two tables is always the same as that of the table to which the trigger is applied. After the trigger is executed, the two tables related to the trigger are also deleted.
The deleted table stores all rows to be deleted from the table due to executing the delete or update statement.
The inserted Table stores all rows to be inserted into the table for execution of the insert or update statement.
Iii. Instead of and after triggers
SQL Server2000 provides two triggers: instead of and after. The difference between the two triggers is that they are activated in the same way ﹕

The instead of trigger is used to replace the T-SQL statement that causes the trigger to execute. In addition to tables, the instead of trigger can also be used for views to expand the update operations supported by views.

The After trigger is executed after an insert, update, or deleted statement and performs a constraint check before the after trigger is activated. The After trigger can only be used for tables.

Each modification action (insert, update, and delete) of a table or view can have an instead of trigger. Each modification action of a table can have multiple after triggers.
4. Trigger Execution Process
If an insert, update, or delete statement violates the constraints, the after trigger will not be executed because the check on the constraints occurs before the after trigger is excited. Therefore, the after trigger cannot exceed the constraints.

The instead of trigger can be executed instead of the action that inspires it. It has just been created in the inserted and deleted tables, and is executed when any other operations have not yet occurred. Because the instead of trigger is executed before the constraint, it can pre-process the constraint.

5. Use T-SQL statements to create a trigger

The basic statement is as follows ﹕
Create trigger trigger_name
On {table_name | view_name}
{For | after | instead}
[Insert, update, delete]
As
SQL _statement

Vi. related examples ﹕
1: Create a trigger in the orders table. When an order record is inserted into the orders table, check whether the status of goods in the goods table is 1 (being sorted) yes, you cannot add this order to the orders table.
Create trigger orderinsert
On orders
After insert
As

Begin transaction
If (select status from goods, inserted
Where goods. Name = inserted. goodsname) = 1
Begin
Print 'the goods is being processed'
Print 'The order cannot be committed'
Rollback transaction -- roll back to avoid joining
End
2: Create an insert trigger in the orders table to reduce the inventory in the corresponding item records of the goods table when adding an order.
Create trigger orderinsert1
On orders
After insert
As

Begin transaction
Update goods set storage = storage-inserted.quantity
From Goods, inserted
Where
Goods. Name = inserted. goodsname
3: Create a deletion trigger in the goods table to achieve cascading deletion of the goods table and the orders table.
Create trigger goodsdelete
On Goods
After Delete
As

Begin transaction
Delete from orders
Where goodsname in
(Select name from deleted)
4: Create an update trigger in the orders table to monitor the orderdate column of the orders table, so that it cannot be manually modified.
Create trigger orderdateupdate
On orders
After update
As

Begin transaction
If Update (orderdate)
Begin
Raiserror ('orderdate cannot be modified', 10, 1)
Rollback transaction
End
5: Create an insert trigger in the orders table to ensure that the goods name inserted to the orders table must exist in the goods table.
Create trigger orderinsert3
On orders
After insert
As

Begin transaction
If (select count (*) from goods, inserted where goods. Name = inserted. goodsname) = 0
Begin
Print 'NO ENTRY in goods for this order'
Rollback transaction
End

Basic trigger knowledge and Examples
: Create trigger tr_name
On Table/View
{For | after | instead of} [update] [,] [insert] [,] [delete]
[With encryption]
As {batch | if Update (col_name) [{and | or} Update (col_name)]}
Description: 1 tr_name: trigger name
2 On Table/view: the table to which the trigger applies. A trigger can only act on one table
3 For and after: Synonymous
4 after and instead of: SQL 2000 new project
Differences between after and instead
After instead
After a trigger event occurs, it is activated instead of the corresponding trigger event and executed.
You can only create a table on a table or view.
5. Insert, update, and delete: three operations for activating a trigger can be performed simultaneously.
6 if Update (col_name): indicates whether the operation has an impact on the specified column. If so, the trigger is activated. In addition, because the delete operation only affects rows, if you use Delete
This statement cannot be used for an operation. (although there is no error in use, it does not make sense to activate the trigger ).
7. Two Special Tables Used for trigger execution: deleted and inserted
Deleted and inserted can be said to be special temporary tables. They are automatically generated by the system when the trigger is activated. Their structures are the same as those of the trigger table, only the stored data is different.
Continued
The following table describes the differences between deleted and inserted data.
Differences between deleted and inserted data
Inserted | deleted
Stores data after insert and update operations | stores data before Delete and update operations
Data
Note: The update operation is equivalent to performing the delete operation before the insert operation. Therefore, when performing the update operation, copy the data before the modification to the deleted table, when the modified data is stored in the table to which the trigger acts, a copy is also generated to the insered table.
Use pubs
Go
Begin tran
Create Table Table1 (column 1 int, column 2 INT)
Create Table Table2 (column 11 int, column 22 INT)
Go
Create trigger t_dalii on Table1
After insert
---- ^ Triggered when inserted
As
Begin
Select * From Table2;
Insert Table2 select * From inserted
------------------------------- ^ Temporary table for storing and inserting temporary data
End
Go
---------- ** When you insert data to Table1, Table2 inserts the same data **--
Insert Table1 values (1, 2)
Select * From Table1
Select * From Table2
Drop table Table1
Drop table Table2
Commit tran