SQL Server triggers create, delete, modify, view

　　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. 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. In addition to tables, the Instead of triggers 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 triggers:

　　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: The Orders table creates an insert trigger that guarantees 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