SQL Server triggers create, delete, modify, view sample code _mssql

One: A trigger is a special stored procedure that cannot be invoked explicitly, but is automatically activated when you insert records into a table ﹑ update records or delete records. So triggers can be used to implement complex integrity constraints on a table.

Two: SQL Server creates two private tables for each trigger: the inserted table and the deleted table. These two tables.

One: A trigger is a special stored procedure that cannot be invoked explicitly, but is automatically activated when you insert records into a table ﹑ update records or delete records. So triggers can be used to implement complex integrity about a table about ' bundle.

Two: SQL Server creates two private tables for each trigger: the inserted table and the deleted table. The two tables are maintained by the system, and they exist in memory rather than in the database. The structure of the two tables is always the same as the structure of the table that the trigger acts on. After the trigger execution completes, the two tables associated with the trigger are also deleted.
The deleted table holds all rows that are deleted from the table because of the execution of the delete or UPDATE statement.
The inserted table holds all the rows that are inserted into the table because of the INSERT or UPDATE statement being executed.
Three: Instead of and after triggers
SQL Server2000 provides two types of triggers: Instead of and after triggers. The difference between the two triggers is that they are activated in the same way:

Instead of triggers are used to override T-SQL statements that cause triggers to execute. ,instead of triggers other than tables can also be used in views to extend the update operations that the view can support.

After a trigger executes after a insert,update or deleted statement, a constraint check occurs before the after trigger is activated. After triggers can only be used for tables.

Each modification action (insert,update and delete) of a table or view can have a instead of trigger, and each modification action of a table can have multiple after triggers.
Four: The execution process of the trigger
If a insert﹑update or DELETE statement violates the constraint, the After trigger does not execute because the check of the constraint occurs before the trigger is excited. So after triggers cannot transcend constraints.

The Instead of trigger can be executed instead of the action that fires it. It has just been established in the inserted table and the deleted table, and any other operation that has not occurred is executed. Because the instead OF trigger executes before the constraint, it can perform some preprocessing of the constraint.

V: using T-SQL statements to create triggers

The basic statements are as follows:
Create TRIGGER Trigger_name
on {table_name | view_name}
{for | After | Instead of}
[Insert, Update,delete]
As
Sql_statement

VI: Delete triggers:

The basic statements are as follows:

Drop Trigger Trigger_name

Seven: View existing triggers in the database:

--View the database already have triggers
Use Jxcsoftware
Go
SELECT * from sysobjects where xtype= ' TR '

--View a single trigger
EXEC sp_helptext ' trigger name '

Eight: Modify the trigger:

The basic statements are as follows:
Alter TRIGGER Trigger_name
on {table_name | view_name}
{for | After | Instead of}
[Insert, Update,delete]
As
Sql_statement

Nine: Related examples:
1: A trigger is established in the Orders table, when an order record is inserted into the Orders table, the item status of the goods table is checked for 1 (being sorted), and the order cannot be added to the Orders table.
Create Trigger OrderInsert
On orders
After insert
As
if (select status from goods,inserted
where Goods.name=inserted.goodsname) =1
Begin
print ' The goods is being processed '
print ' The order cannot to be committed '
ROLLBACK TRANSACTION-rollback to avoid joining
End
2: Create an INSERT trigger in the Orders table and reduce the inventory in the corresponding item record of the goods table when adding an order.
Create Trigger Orderinsert1
On orders
After insert
As
Update goods Set storage=storage-inserted.quantity
From goods,inserted
where
Goods.name=inserted.goodsname
3: Create a DELETE trigger on the goods table to implement cascading deletion of the goods table and Orders table.
Create Trigger Goodsdelete
On goods
After delete
As
Delete from Orders
where Goodsname in
(select name from deleted)
4: Create an UPDATE trigger on the Orders table and monitor the Order date (OrderDate) column of the Orders table so that it cannot be modified manually.
Create Trigger Orderdateupdate
On orders
After update
As
If Update (OrderDate)
Begin
RAISERROR (' OrderDate cannot be modified ', 10, 1)
ROLLBACK TRANSACTION
End
5: Create an INSERT trigger on the Orders table to ensure that the name of the goods inserted into the Orders table must exist in the goods table.
Create Trigger Orderinsert3
On orders
After insert
As
if (select COUNT (*) from goods,inserted where Goods.name=inserted.goodsname) =0
Begin
print ' No entry in goods '
ROLLBACK TRANSACTION
End

6:orders table creates an insert trigger to ensure that the item information inserted into the Orders table is added to the order table

Alter TRIGGER AddOrder
On Orders
For insert
As
INSERT INTO order
Select inserted. Id, inserted.goodname,inserted. Number from inserted