T-SQL statement create trigger

Create trigger name
On Table or view
For | after | instead of -- operation time
Insert, update, delete
As
SQL statement

 

Example 1:
Requirement: Create an insert trigger in the order_test table. When a row is inserted into the order_test table, if the cust_test table corresponds

The record status value is 1, indicating that the data cannot be written in the preparation status.

Create trigger cust_orders_ins2
On order_test
After insert
As
If (select cstatus from cust_test, inserted where

Cust_test.customerid = inserted. customerid) = 1
Begin
Print 'the goods is being processed'
Rollback transaction
End
Go

Example 2:

Create an insert trigger on the order_test table to reduce the inventory of the corresponding goods in the cust_test table when adding an order.

Create trigger cust_orders_ins3
On order_test
After insert
As
Update cust_test set cstorage = cstorage-inserted.orders
From cust_test, inserted
Where cust_test.customerid = inserted. customerid

Example 3:

Create an insert trigger on the order_test table. The specified order date (odate) cannot be modified manually.

Create trigger orderdateupdate
On order_test
After update
As
If Update (odate)
Begin
Raiserror ('error', 10, 1)
Rollback transaction
End

Example 4:

The required items must be in the warehouse and the quantity is sufficient.

Create trigger order_insert5
On order_test
After insert
As
Begin
If (select count (*)
From cust_test, inserted
Where cust_test.customerid = inserted. customerid) = 0
Begin
Print 'NO ENTRY in goods for your order'
Rollback transaction
End
If (select cust_test.cstorage from cust_test, inserted
Where cust_test.customerid = inserted. customerid) <
(Select inserted. Orders from cust_test, inserted
Where cust_test.customerid = inserted. customerid)
Begin
Print 'no enough entry in goods for your order'
Rollback transaction
End

Example 6:

When an insert trigger is created on the order_test table and multiple rows of data are inserted at the same time, the purchased items must be included in the warehouse

Create trigger order_insert6
On order_test
After insert
As
If
(Select count (*) from cust_test, inserted
Where cust_test.customerid = inserted. customerid) <> @ rowcount
-- The @ rowcount function can be used in the trigger logic to differentiate single-row insertion and multi-row insertion.
Begin
Delete order_test from order_test, inserted
Where order_test.orderid = inserted. orderid and
Inserted. customerid not in (select customerid from cust_test)
End

Print @ rowcount