触发器

use northwind;
go
/****************************************************
 * 删除cust_test表的记录时，
 * 同时删除order_test表中的关联数据
 ****************************************************/
create trigger cust_order_del1
on cust_test
after delete
as
delete from order_test
where customerID in 
(
select id from deleted
)
go

/****************************************************
 * 插入order_test表时，如果cust_test表中关联记录的
 * locked字段为1，则事务回滚，不允许插入
 ****************************************************/
create trigger cust_order_ins2
on order_test
after insert
as
if(select locked from cust_test, inserted where cust_test.id=inserted.customerID)=1
begin
print 'The goods is beging processed'
rollback transaction
end

go

/****************************************************
 * 插入order_test记录时，关联的cust_test记录中的数量要减少
 ****************************************************/
create trigger order_cust_ins3
on order_test
after insert
as
update cust_test set cust_test.account=cust_test.account-inserted.account
from cust_test, inserted 
where cust_test.id=inserted.customerid

/****************************************************
 * 不允许修改orderDate字段。（在一次性UPDATE多条数据时，该触发器出错）
 ****************************************************/
create trigger order_upd4
on order_test
after update
as
if(select orderdate from inserted) is not null
begin
print 'can not update orderdate'
rollback transaction
end

/****************************************************
 * 不允许修改orderDate字段。（允许一次性UPDATE多条数据）
 ****************************************************/
ALTER trigger [order_upd4]
on [dbo].[Order_test]
after update
as
if update(orderDate)
begin
print 'can not update orderdate'
rollback transaction
end