Sql Server 触发器 实例

妖怪幻

五﹕使用T-SQL语句来创建触发器    基本语句如下﹕                    create trigger trigger_name            on {table_name | view_name}            {for | After | Instead of }            [ insert, update,delete ]            as            sql_statement            六﹕相关示例﹕          1﹕在Orders表中建立触发器﹐当向Orders表中插入一条订单记录时﹐检查goods表的货品状态status是否为1(正在整理)﹐是﹐则不能往Orders表加入该订单。 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   --回滚﹐避免加入 end 2﹕在Orders表建立一个插入触发器﹐在添加一条订单时﹐减少Goods表相应的货品记录中的库存。 create trigger orderinsert1 on orders after insert as update goods set storage=storage-inserted.quantity from goods,inserted where goods.name=inserted.goodsname 3﹕在Goods表建立删除触发器﹐实现Goods表和Orders表的级联删除。 create trigger goodsdelete on goods after delete as delete from orders where goodsname in (select name from deleted) 4﹕在Orders表建立一个更新触发器﹐监视Orders表的订单日期(OrderDate)列﹐使其不能手工修改. create trigger orderdateupdate on orders after update as if update(orderdate) begin raiserror(' orderdate cannot be modified',10,1) rollback transaction end 5﹕在Orders表建立一个插入触发器﹐保证向Orders表插入的货品名必须要在Goods表中一定存在。 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. 这两张表中的course相关联，我需要修改depart_course表中的course数据时，course表能够自动更新 　　例如：depart_course表中的course字段有一个是计算机原理，而course表中也有计算机原理这门课程的相关信息，也同样放在course表中的course字段 　　 　　CREATE TRIGGER up ON depart_course FOR UPDATE AS 　　if Update(course) begin Update course Set course=i.course From course, Deleted d ,Inserted i Where course.course=d.course 　　end