Create, delete, modify, and view SQL Server triggers

　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.

　　3. 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 the constraints are checked 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

　　6. delete a trigger:

　　The basic statement is as follows:

Drop trigger trigger_name

　　7. view existing triggers in the database:

-- View existing triggers in the database

Use jxcSoftware

Go

Select * from sysobjects where xtype = 'tr'

-- View a single trigger

Exec sp_helptext 'trigger name'

　　8. Modify the trigger:

　　The basic statement is as follows:

Alter trigger trigger_name

On {table_name | view_name}

{For | After | Instead}

[Insert, update, delete]

As

SQL _statement

　　9. Example:

　　1:Create a trigger in the Orders table. When an order record is inserted into the Orders table, check whether the status of the 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

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 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

Update goods set storage = storage-inserted.quantity

From goods, inserted

Where

Goods. name = inserted. goodsname

　　3:Create a delete 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

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

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

If (select count (*) from goods, inserted where goods. name = inserted. goodsname) = 0

Begin

Print 'NO entry in goods for this order'

Rollback transaction

End

　　6:Create an insert trigger for the Orders table to ensure that the product information inserted to the Orders table must be 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