SQL Server trigger creation, deletion, modification, view

　One: A trigger is a special stored procedure that cannot be called explicitly, but is automatically activated when a record is inserted, updated, or deleted into the table. So triggers can be used to implement complex integrity constraints on tables.

　Two: SQL Server creates two dedicated tables for each trigger: Inserted table and Deleted table. These two tables are maintained by the system and exist in memory rather than in the database. The structure of these two tables is always the same as the structure of the table acted upon by the trigger. After the trigger execution is completed, the two tables related to the trigger are also deleted.

　The Deleted table stores all rows that are to be deleted from the table due to the execution of Delete or Update statements.

　The Inserted table stores all rows to be inserted into the table due to the execution of the Insert or Update statement.

　Three: Instead of and After triggers

　SQL Server2000 provides two types of triggers: Instead of and After triggers. The difference between these two triggers is that they are activated in the same way:

　Instead of trigger is used to replace the T-SQL statement that causes the trigger to execute. In addition to tables, Instead of triggers can also be used on views to extend the update operations that views can support.

　The After trigger is executed after an Insert, Update or Deleted statement, and actions such as constraint checking occur before the After trigger is activated. After triggers can only be used on tables.

　 Each modification action (insert, update and delete) of a table or view can have an instead of trigger, and each modification action of a table can have multiple After triggers.

　IV: Trigger execution process

　If an Insert, update or delete statement violates a constraint, the After trigger will not execute because the check of the constraint occurs before the After trigger is excited. So the After trigger cannot exceed the constraints.

　Instead of A trigger can be executed instead of the action that fired it. It is executed when the Inserted table and Deleted table have just been created and no other operations have occurred. Because the Instead of trigger executes before the constraint, it can do some preprocessing of the constraint.

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

　 Six: Delete trigger:

　 The basic statement is as follows:

　 drop trigger trigger_name

　 Seven: 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'

　 Eight: Modify trigger:

　The basic statement is 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: In the Orders table Create a trigger in the Orders table. When inserting an order record into the Orders table, check whether the status of the goods in the goods table is 1 (organizing). If so, 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 be committed'

　rollback transaction --rollback, avoid adding

　end

　2: Create an insert trigger in the Orders table, and when adding an order, reduce the corresponding number of the Goods table Inventory in item records.

　create trigger orderinsert1

　on orders

　after insert

　as

　update goods set storage=storage-inserted.quantity

　from goods,inserted

　where

　goods.name=inser ted.goodsname

　3: Create a delete trigger in the Goods table to implement cascade 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 in the Orders table to monitor Order s table The order date (OrderDate) column 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 in the Orders table to ensure that the product name 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

6: Create an insert trigger in the Orders table to ensure that the product information inserted into the Orders table will be added to the Order table. Id, inserted.goodName,inserted.Number from inserted