T-SQL语句创建触发器

create trigger 触发器名
on 表或视图
for|after|instead of --操作时机
insert,update,delete
as
sql语句

 

例1:
 要求:在order_test表建立insert触发器，当向order_test表插入一行，如果cust_test表中对应

 记录status值为1，说明处于准备状态不能写入该数据

 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

例2:

在order_test表上建立一个插入触发器，在添加一个订单时，减少cust_test表的相应货物的记录的库存量。

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

例3:

 在order_test表上建立一个插入触发器，规定订单日期(Odate)不能手工修改。

create trigger orderdateupdate
on order_test
after update
as
if update (odate)
begin
raiserror('Error',10,1)
rollback transaction
end

例4:

 要求订购的物品一定要在仓库中有的，并且数量足够。

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

例6:

 在order_test表上建立一个插入触发器，同时插入多行数据时，要求订购的物品一定要在仓库中有的

create trigger order_insert6
on order_test
after insert
as
if
(select count(*) from cust_test,inserted
where cust_test.customerid=inserted.customerid)<>@@rowcount
--可以在触发器逻辑中使用 @@ROWCOUNT 函数以区分单行插入和多行插入。
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