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字段 ?****************************************************/ 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
?