Shulou(Shulou.com)05/31 Report--

This article introduces how to create, delete, modify and view the code of SQLServer trigger. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

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.

Second: SQL Server creates two dedicated tables for each trigger: the Inserted table and the Deleted table. These two watches. 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 bundles on tables. Second: SQL Server creates two dedicated tables for each trigger: the Inserted table and the Deleted table. These two tables are maintained by the system, and they exist in memory rather than in the database. The structure of these two tables is always the same as that of the table being acted on by the trigger. After the trigger is executed, the two tables associated with the trigger are also deleted. The Deleted table holds all rows that are deleted from the table as a result of executing Delete or Update statements. The Inserted table holds all rows to be inserted into the table as a result of executing Insert or Update statements.

Instead of and After triggers SQL Server2000 provides two kinds of triggers: Instead of and After triggers. The difference between these two types of triggers is that they are activated in the same way: Instead of triggers are used to replace the T-SQL statements that cause the trigger to execute. In addition to tables, Instead of triggers can also be used for views to extend the update operations that views can support. After triggers execute after an Insert,Update or Deleted statement, and constraint checking occurs before the After trigger is activated. After triggers can only be used for tables. Each modification action (insert,update and delete) for a table or view can have one instead of trigger, and each modification action for a table can have multiple After triggers.

Fourth: trigger execution process if an Insert, update, or delete statement violates the constraint, then the After trigger will not execute, because the constraint check occurs before the After trigger is activated. So After triggers cannot go beyond the constraint. Instead of triggers can be performed instead of the actions that fire 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 do some preprocessing to the constraint.

Five: the basic statement to create a trigger using T-SQL statement is as follows: create trigger trigger_name on {table_name | view_name} {for | After | Instead of} [insert, update,delete] as sql_statement

6: delete 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'

Eighth: modify the 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

9: related example: 1: establish a trigger in the Orders table. When inserting an order record into the Orders table, check whether the item status status of the goods table is 1 (being sorted out). Yes, 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 to avoid adding end 2: create an insert trigger in the Orders table to reduce the inventory in the corresponding item record in 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: set up a delete trigger in the Goods table to achieve the 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 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 in the Orders table to ensure that the item 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:Orders table establishes an insert trigger to ensure that the item information inserted into the Orders table will add alter trigger addOrder on Orders for insert as insert into Order select inserted.Id, inserted.goodName,inserted.Number from inserted to the Order table

On the SQLServer trigger creation, deletion and modification and how to view the code to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.