SQL Server triggers create, delete, modify, view sample steps

A: A trigger is a special stored procedure that cannot be called explicitly, but is automatically activated when a record is inserted into a table ﹑ a record is updated or a record is deleted. So the trigger can be used to implement complex completeness about the table to the ' bundle.

Two:sql server created two dedicated table:inserted tables and deleted tables for each trigger. These two tables.

A: A trigger is a special stored procedure that cannot be called explicitly, but is automatically activated when a record is inserted into a table ﹑ a record is updated or a record is deleted. So the trigger can be used to implement complex completeness about the table to the ' bundle.

Two:sql server created two dedicated table:inserted tables and deleted tables for each trigger. These 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. The two tables associated with the trigger are also deleted after the trigger execution is complete.
The deleted table holds all rows that are to be removed from the table because of execution of the DELETE or UPDATE statement.
The inserted table holds all rows to be inserted into the table because of an INSERT or UPDATE statement execution.
Three:instead of and after triggers
SQL Server2000 provides two trigger: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 replace T-SQL statements that cause trigger execution. In addition to tables,instead of triggers can also be used for views that extend the update operations that the view can support.

After triggers are executed after a insert,update or deleted statement, and actions such as constraint checking occur 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 more than one after trigger.
Four: The execution of the trigger process
If a insert﹑update or DELETE statement violates the constraint, then the after trigger does not execute because the check for the constraint occurs before the after trigger is agitated. So after triggers cannot go beyond constraints.

The Instead of trigger can be executed in place of the action that fired it. It is executed when the inserted table and the deleted table have just been created and no other operations have occurred. Because the instead OF trigger executes before the constraint, it can perform some preprocessing on the constraint.

V: use 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 trigger:

The basic statements are as follows:

Drop Trigger Trigger_name

Seven: View the existing triggers in the database:

--View the database already has a trigger
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 status of the goods table is checked for 1 (being collated), yes, the order is not 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 be committed '
ROLLBACK TRANSACTION--rollback, avoid joining
End
2: creates an insert trigger on the Orders table, reducing the inventory in the corresponding item record in the goods table when an order is added.
Create Trigger Orderinsert1
On orders
After insert
As
Update goods Set storage=storage-inserted.quantity
From goods,inserted
where
Goods.name=inserted.goodsname
3: deletes the goods table and the Orders table by creating a delete trigger on the goods table.
Create Trigger Goodsdelete
On goods
After delete
As
Delete from Orders
where Goodsname in
(select name from deleted)
4: an UPDATE trigger is established on the Orders table to 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: An INSERT trigger is created in the Orders table to ensure that the name of the item to be 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 for this order '
ROLLBACK TRANSACTION
End

The 6:orders table establishes an INSERT trigger that ensures 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

SQL Server triggers create, delete, modify, view sample steps