触发器作业.doc

设计一个触发器，该触发器仅允许dbo用户可以删除employee3266表内数据，否则出错。create trigger employeedelete on employee3266for deleteas if exists(select *from deleted) begin if user!=dbo begin print 你无权删除！ rollback end enddrop trigger employeedelete 测试：（以sa用户）delete from employee3266where employeeno=E2005001测试：（以其他用户）sp_addlogin wf,wf/*登录名 密码*/sp_adduser wf,wfgrant all privileges on employee3266 to wf;/*退出DBMS，重新以wf身份登入DBMS，执行删除操作*/delete from employee3266where employeeno=E2005001在ordermaster3266表中创建触发器，插入数据时要先检查employee3266表中是否存在和ordermaster3266表同样值的业务员编号，如果不存在则不允许插入。create trigger OrderMasterIns on OrderMaster3266for insertas if not exists(select b.saleNo from employee3266 a,inserted b,ordermaster3266 c where b.saleno=a.employeeNo) begin print 插入数据错误！ rollback end检验：insert OrderMaster3266 values(200806135578,C20050001,E2009008,0.00,20080612,20080615,I000000010)请完成下面实验内容：(1) 创建触发器，该触发器仅允许“dbo”用户可以删除Employee表内数据。create trigger empdeton employee3266for deleteas if exists(select * from deleted) begin if user!=dbo rollback end测试一：exec sp_addlogin wfexec sp_adduser wfgrant all privileges on employee3266 to wf;delete from employee3266where employeeno=E2005001 测试二delete from employee3266where employeeno=E2005001(2) 创建触发器，保证Employee表中性别为”F”或”M”。 create trigger emp_sexon employee3266for insert,updateas if exists(select *from inserted where sex not in (F,M) rollback测试一：update employee3266 set sex=Gfrom employee3266where employeeno=E2005005测试二：update employee3266 set sex=Ffrom employee3266where employeeno=E2005005(3) 创建触发器，要求当修改Employee表的员工生日或雇佣日期时必须保证出生日期在雇佣日期之前，且年龄不小于岁，雇佣日期与出生日期必须间隔在年之上。create trigger employupton employee3266for updateas if update(birthday) or update(hiredate) begin declare birthday datetime,hiredate datetime select birthday=birthday,hiredate=hiredate from deleted if year(getdate()-year(birthday)25 rollback else if year(hiredate)-year(birthday)1 begin print 此删除操作可能会删除多条人事表数据! rollback transaction end else begin update ordermaster3266 set customerno=inserted.customerno from deleted,inserted where ordermaster3266.customerno=deleted.customerno endendalter table OrderMaster3266 drop constraint OrderMaster3266FK1update employee2689set employeeNo2689=C20080002where employeeNo2689=C20050001(5) 创建触发器，当业务员总销售业绩超过100000元时，其薪水自动增加20%，当业务员总销售业绩超过50000元低于100000时，其薪水自动增加10%，当业务员总销售业绩低于10000时，其薪水自动减少10%。create trigger salaryupton employee3266for updateas begin declare totsum numeric(9,2),employeeno char(8) declare emp_cur cursor for select saleno,sum(ordersum) from ordermaster3266 group by saleno open emp_cur fetch emp_cur into employeeno,totsum while(fetch_status=0) begin if totsum10000 begin update employee3266 set salary=b.salary*1.2 from employee3266 a,deleted b where a.employeeno=b.employeeno and a.employeeno=employeeno end else if totsum5000 begin update employee3266 set salary=b.salary*1.1 from employee3266 a,deleted b where a.employeeno=b.employeeno and a.employeeno=employeeno end else if totsummonth(b.hiredate)+3)or(year(getdate()=year(b.hiredate)+1 and month(getdate()3-12+month(b.hiredate)and