(1) 设置一个触发器，该触发器仅允许“dbo”用户可以删除Employee表内数据，否则出错。

create trigger EmploteeDelete on employeefor deleteasif exists (select * from deleted)beginif user!='dbo'rollbackend(2) 在OrderMaster表中创建触发器，插入数据时要先检查Employee表中是否存在同样值的业务员编号，如果不存在则不允许插入。

create trigger insOrderMasteron OrderMasterfor insertasbeginif exists(select*from insertedwhere salerNo not in(select employeeNofrom Employee))rollback;end(3) 级联更新：当更新Customer表中customerNo列的值时，同时更新OrderMaster表中的customerNo列的值，并且一次只能更新一行。

create trigger uptCustomeron Customerfor updateasdeclare @oldCustomerNo char(9),@newCustomerNo char(9)if(select count(*)from inserted)>1rollbackelsebeginif update(customerNo)beginselect @oldCustomerNo=customerNo from deletedselect @newCustomerNo=customerNo from insertedupdate OrderMaster set customerNo=@newCustomerNowhere customerNo=@oldCustomerNoendend(4) 对Product表写一个UPDATE触发器。

当更新“主板”类的商品时，其单价不得高于原价格的10% create trigger uptProducton Productfor updateasbegindeclare @oldPrice numeric(7,2),@newPrice numeric(7,2)if update(productPrice)begindeclare myCur cursor forselect a.productPrice,b.productPricefrom inserted a,deleted bwhere a.productNo=b.productNo and b.productClass='主板' open myCurfetch myCur into @newPrice,@oldPricewhile(@@fetch_status=0)beginif @newPrice>@oldPrice+@oldPrice*0.1rollbackfetch myCur into @newPrice,@oldPriceendclose myCurdeallocate myCurendend(5) 保证商品的销售单价必须在成本价的正负10%之内插入触发器create trigger insOrderDetailon OrderDetailfor insertasbegindeclare @productNo char(9),@price numeric(7,2)declare @productPrice numeric(7,2),@orderNo char(12)declare myCur cursor forselect orderNo,b.productNo,price,productPricefrom Product a,inserted bwhere a.productNo=b.productNoopen myCurfetch myCur into @orderNo,@productNo,@price,@productPrice while(@@fetch_status=0)beginif(@price>@productPrice+@productPrice*0.1 or@price<@productPrice-@productPrice*0.1)delete from OrderDetailwhere orderNo=@orderNo and productNo=@productNo fetch myCur into @orderNo,@productNo,@price,@productPrice endclose myCurdeallocate myCurend更新触发器create trigger uptOrderDetailon OrderDetailfor updateasbegindeclare @productNo char(9),@oldPrice numeric(7,2)declare @newPrice numeric(7,2)declare @productPrice numeric(7,2),@orderNo char(12)if update(price)begindeclare myCur cursor forselect b.orderNo,b.productNo,b.price,c.price,productPricefrom Product a,inserted b,deleted cwhere a.productNo=b.productNo and a.productNo=c.productNo and b.orderNo=c.orderNoopen myCurfetch myCur into@orderNo,@productNo,@newPrice,@oldPrice,@productPrice while(@@fetch_status=0)beginif(@newPrice>@productPrice+@productPrice*0.1 or@newPrice<@productPrice-@productPrice*0.1)update OrderDetail set price=@oldPricewhere orderNo=@orderNo and productNo=@productNo fetch myCur into@orderNo,@productNo,@newPrice,@oldPrice,@productPrice endclose myCurdeallocate myCurendend(6) 保证员工编码的第2~5位与员工的雇佣日期的年份一致。

插入触发器create trigger insEmploteeon employeefor insertasbegindeclare @employeeNo char(8),@hireDate datetimedeclare @subEmployeeNo char(4)declare myCur cursor forselect employeeNo,hireDatefrom insertedopen myCurfetch myCur into @employeeNo,@hireDatewhile(@@fetch_status=0)beginset @subEmployeeNo=substring(@employeeNo,2,4)if(@subEmployeeNo <>convert(char(4),@hireDate,120))delete from Employeewhere employeeNo=@employeeNofetch myCur into @employeeNo,@hireDateendclose myCurdeallocate myCurend更新触发器create trigger uptEmploteeon employeefor updateasbegindeclare @employeeNo char(8),@hireDate datetimedeclare @subEmployeeNo char(4)if (update(employeeNo)or update(hireDate))begindeclare myCur cursor forselect employeeNo,hireDatefrom insertedopen myCurfetch myCur into @employeeNo,@hireDatewhile(@@fetch_status=0)beginset @subEmployeeNo=substring(@employeeNo,2,4)if(@subEmployeeNo <>convert(char(4),@hireDate,120))rollbackfetch myCur into @employeeNo,@hireDateendclose myCurdeallocate myCurendend。