SQL Server trigger usage

SQL trigger instance 1

Definition: What is a trigger? In SQL Server, certain operations on a table trigger certain conditions to execute a program. A trigger is a special stored procedure.
There are three common triggers: Insert, Update, and Delete events.

Why should I use a trigger? For example, two tables:

The code is as follows:	Copy code
Create Table Student (-- Student Table StudentID int primary key, -- student ID ....        ) Create Table BorrowRecord (-- student borrow record Table BorrowRecord int identity (1, 1), -- serial number StudentID int, -- student ID BorrowDate datetime, -- lending time ReturnDAte Datetime, -- return time ...       )

The following functions are used:
1. If I change the student's student ID, I hope that his borrowing record will still be related to this student (that is, changing the student ID of the borrowing record at the same time );
2. If the student has graduated, I want to delete his student ID and his/her borrowing history.
And so on.

A trigger can be used at this time. For 1, create an Update trigger:

The code is as follows:	Copy code
Create Trigger truStudent On Student -- create a trigger in the Student table For Update -- why events are triggered As-what to do after an event is triggered If Update (StudentID) Begin Update BorrowRecord Set StudentID = I. StudentID From BorrowRecord br, Deleted d, Inserted I -- Deleted and Inserted temporary tables Where br. StudentID = d. StudentID End

                 
Understand the two temporary tables in the trigger: Deleted and Inserted. Note: Deleted and Inserted indicate the "old record" and "new record" tables that trigger the event respectively ".
There are two virtual tables in a database system that are used to store the changes recorded in the table:
Virtual Table Inserted virtual table Deleted

New records are stored when table records are added. New records are not stored.
New record used for update stored during modification
The deleted records are not stored during deletion.

An Update process can be viewed as: generate a new record to the Inserted table, copy the old record to the Deleted table, delete the Student record, and write a new record.

For 2, create a Delete trigger

The code is as follows:	Copy code
Create trigger trdStudent On Student For Delete As Delete BorrowRecord From BorrowRecord br, Delted d Where br. StudentID = d. StudentID

From the two examples, we can see the key of the trigger: A.2 temporary tables; B. Trigger mechanism.

Instance 2

The code is as follows:

Copy code

Select * from sysobjects where xtype = 'tr' -- View the triggers Create trigger Ma_Importance_INSERT ON qyml For insert/* INSERT, UPDATE, DELETE */ AS Begin Declare @ InsertID int Select @ InsertID = id from INSERTED -- find the added record content Insert into Ma_Importance (cid, ichar) values (@ InsertID, 'D ') End Create trigger Ma_Importance_DELETE ON qyml For delete/* INSERT, UPDATE, DELETE */ AS Begin Declare @ deleteID int Select @ deleteID = id from DELETED -- query the content of a DELETED record Delete from Ma_Importance where cid = @ deleteID End Drop TRIGGER ma_inportance_insert

Appendix I read/write creation and deletion of a trigger

I? Is a trigger a special stored procedure? Cannot It be explicitly called? But insert records into the table? It is automatically activated when a record is updated or deleted. Therefore, triggers can be used to implement complex integrity constraints on tables.

 

2? Does SQL Server create two dedicated tables for each trigger? Inserted table and Deleted table. These two tables.

 

I? Is a trigger a special stored procedure? Cannot It be explicitly called? But insert records into the table? It is automatically activated when a record is updated or deleted. Therefore, triggers can be used to implement complex integrity constraints on tables.
        

2? Does SQL Server create two dedicated tables for each trigger? Inserted table and Deleted table. Are these two tables maintained by the system? They exist in memory rather than 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
Does SQL Server2000 provide two triggers? Instead of and After triggers. 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. Besides tables? Can an Instead of trigger be used for a view? It is used to extend the update operations supported by the view.
       

After trigger is executed After an Insert, Update, or Deleted statement? The constraints check and other actions occur 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? Is the update or delete statement in violation of the constraints? So the After trigger will not be executed? Because constraints are checked 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. Is it just created in the Inserted and Deleted tables? Any other operation has not been performed yet. Because the Instead of trigger is executed before the constraint? Therefore, it can pre-process constraints.
       

5? Create a trigger using T-SQL statements

The basic statements are as follows?

The code is as follows:	Copy code
Create trigger trigger_name On {table_name | view_name} {For | After | Instead} [Insert, update, delete] As SQL _statement

6? Delete trigger:

 

The basic statements are as follows?

The code is as follows:	Copy code
Drop trigger trigger_name

7. View existing triggers in the database:

The code is as follows:	Copy code
-- 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 statements are as follows?

The code is as follows:	Copy code
Alter trigger trigger_name On {table_name | view_name} {For | After | Instead} [Insert, update, delete] As SQL _statement

         

9? Related examples?

1? Create a trigger in the Orders table? When an order record is inserted into the Orders table? Check whether the item status of the goods table is

1 (finishing )? Yes? You cannot add this order to the Orders table.

The code is as follows:	Copy code
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 joining End

2? Create an insert trigger in the Orders table? When I add an order? Reduce the inventory in the corresponding product Records in the Goods table.

The code is as follows:	Copy code
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? Implement Cascading deletion of Goods tables and Orders tables.

The code is as follows:	Copy code
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? What is the order date column in the monitoring Orders table? So that it cannot be manually modified.

The code is as follows:	Copy code
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? Ensure that the product name inserted to the Orders table must exist in the Goods table.

The code is as follows:	Copy code
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.

The code is as follows:	Copy code
Alter trigger addOrder On Orders For insert As Insert into Order Select inserted. Id, inserted. goodName, inserted. Number from inserted