SQL Trigger (Trigger)

Let's take a look at a short procedure.

The following three tables are available:

Account (number, name, balance, establishment date, savings Bank number)

Savings Bank (number, name, address, number of persons, city)

Borrowing (account, type of loan, amount, date)

1 Create TriggerTri_bank_delete2  onBank for Delete  as3 Declare @count_account_of_bank int4 Select @count_account_of_bank=COUNT(*)5  from Account6 wherebank_id=(Selectbank_id fromdeleted)7 Group  bybank_id8 if @count_account_of_bank>09 beginTen     Print'There are account information in the savings bank, not to be deleted! ' One     rollback Transaction A End

This is a simple small program to create triggers. Let's take a look at the definition and use of triggers

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 triggers can be used to implement complex integrity constraints on a table.

Two: 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 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 types of triggers: Instead of and after triggers. The difference between the two triggers is that they are activated by the same:   the

Instead of triggers are used to override the 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: Execution of triggers  
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 after trigger is agitated. So after triggers cannot go beyond constraints. &NBSP

The Instead of trigger can be executed in lieu 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. &NBSP

Five: Use T-SQL statements to create triggers  

Basic statements are as follows:  

1 Create Trigger  2on|  3 {for| |  of  4[ ] 5 as 6

VI: Delete trigger:  

The basic statement is 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 Trigger: /strong> 

The basic statement is as follows:  

1 Alter Trigger  2on|  3 {for| |  of  4[ ] 5 as 6

Nine: Related examples:
1: When 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, it cannot be added to the Orders table.

1 Create TriggerOrderInsert2  onorders3AfterInsert 4  as 5 if(SelectStatus fromgoods,inserted6 whereGoods.name=Inserted.goodsname)=1 7 begin 8 Print 'The goods is being processed' 9 Print 'The order cannot be committed' Ten rollback Transaction --Rollback , avoid joining One End 

2: Create an INSERT trigger in the Orders table to reduce inventory in the corresponding item record in the goods table when an order is added.

1 Create TriggerOrderinsert12  onorders3AfterInsert 4  as 5 UpdateGoodsSetStorage=Storage-inserted.quantity6  fromgoods,inserted7 where 8Goods.name=Inserted.goodsname

3: Create a DELETE trigger on the goods table to implement cascading deletions of the goods table and the Orders table.

1 Create Trigger  2 on 3 Delete 4 as 5 delete    from 6 Where in 7 (select  from

4: Create an UPDATE trigger on the Orders table to monitor the order date (OrderDate) column of the Orders table so that it cannot be modified manually.

1 Create Triggerorderdateupdate2  onorders3AfterUpdate 4  as 5 if Update(OrderDate)6 begin 7 RAISERROR('OrderDate cannot be modified',Ten,1) 8 rollback Transaction 9 End 

5: Create an INSERT trigger on the Orders table to ensure that the name of the item to be inserted into the Orders table must exist in the goods table.

1 Create TriggerOrderinsert32  onorders3AfterInsert 4  as 5 if(Select Count(*) fromgoods,insertedwhereGoods.name=Inserted.goodsname)=0 6 begin 7 Print 'no entry in goods for this order' 8 rollback Transaction 9 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

1 Alter Trigger  2 on 3 for insert 4 as 5  InsertintoOrder6Select inserted. Id, inserted.goodname,inserted.  Number  from inserted

SQL Trigger (Trigger)