触发器作业.doc

设计一个触发器，该触发器仅允许dbo用户可以删除employee3266表内数据，否则出错。
create trigger employeedelete
on employee3266
for delete
as
if exists(select *from deleted)
begin
if user!='dbo'
begin
print '你无权删除！'
rollback
end
end

drop trigger employeedelete

测试：（以sa用户）
delete from employee3266
where employeeno='E2005001'

测试：（以其他用户）
sp_addlogin 'wf','wf'/*——————登录名 密码*/
sp_adduser 'wf','wf'

grant all privileges
on employee3266
to wf;
/*退出DBMS，重新以wf身份登入DBMS，执行删除操作*/

delete from employee3266
where employeeno='E2005001'

在ordermaster3266表中创建触发器，插入数据时要先检查employee3266表中是否存在和ordermaster3266表同样值的业务员编号，如果不存在则不允许插入。
create trigger OrderMasterIns on OrderMaster3266
for insert
as
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 empdet
on employee3266
for delete
as
if exists(select * from deleted)
begin
if user!='dbo' rollback
end
测试一：
exec sp_addlogin wf
exec sp_adduser wf
grant all privileges
on employee3266
to wf;

delete
from employee3266
where employeeno='E2005001'

测试二
delete
from employee3266
where employeeno='E2005001'

(2) 创建触发器，保证Employee表中性别为”F”或”M”。
create trigger emp_sex
on employee3266
for insert,update
as
if exists(select *from inserted where sex not in ('F','M'))
rollback

测试一：
update employee3266 set sex='G'
from employee3266
where employeeno='E2005005'

测试二：
update employee3266 set sex='F'
from employee3266
where employeeno='E2005005'

(3) 创建触发器，要求当修改Employee表的员工生日或雇佣日期时必须保证出生日期在雇佣日期之前，且年龄不小于２５岁，雇佣日期与出生日期必须间隔在２０年之上。
create trigger employupt
on employee3266
for update
as
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)<20
rollback
else
begin
select @birthday=birthday,@hiredate=hiredate
from inserted
update employee3266 set birthday=@birthday
from employee3266 a,inserted b
where a.employeeno=b.employeeno
update employee3266 set hiredate=@hiredate
from employee3266 a,inserted b
where a.employeeno=b.employeeno
end
End
测试一：
update employee3266 set hiredate=1993-3-28
from employee3266
where employeeno='E2005002'

测试二：
update employee3266 set hiredate='1985-05-17'
from employee3266
where employeeno='E2005004'

(4) 当更新Customer表中的customerNo列的值的时候，同时更新OrderMaster表中的customerNo列的值，并且一次只能更新一行。
create trigger cusupt
on customer3266
for update
as
if update(customerno)
begin
declare @customerno1 char(9),@customerno2 char(9)
declare cus_cur cursor for
select a.customerno,b.customerno
from deleted a,inserted b
open cus_cur
fetch cus_cur into @customerno1,@customerno2
while(@@fetch_status=0)
begin
update customer3266 set customerno=@customerno2
where customerno=@customerno1
update ordermaster3266 set customerno=@customerno2
where customerno=@customerno1
fetch cus_cur into @customerno1,@customerno2
end
close cus_cur
deallocate cus_cur
end

alter table OrderMaster3266 drop constraint fk_OrderMaster3266_customer3266
update customer3266 set customerno='C20080002'
where customerno='C20050001'

create trigger up_customer
on customer3266
for update
as
if update (customerno)
begin
declare @row_cnt int
select @row_cnt=count(*) from deleted
if @row_cnt>1
begin
print '此删除操作可能会删除多条人事表数据!!!'
rollback transaction
end
else
begin
update ordermaster3266 set customerno=inserted.customerno
from deleted,inserted
where ordermaster3266.customerno=deleted.customerno
end
end

alter table OrderMaster3266 drop constraint OrderMaster3266FK1
update employee2689
set employeeNo2689='C20080002'
where employeeNo2689='C20050001'

(5) 创建触发器，当业务员总销售业绩超过100000元时，其薪水自动增加20%，当业务员总销售业绩超过50000元低于100000时，其薪水自动增加10%，当业务员总销售业绩低于10000时，其薪水自动减少10%。
create trigger salaryupt
on employee3266
for update
as
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 @totsum>10000
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 @totsum<10000 and @totsum>5000
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 @totsum<1000
begin
update employee3266 set salary=b.salary*0.9
from employee3266 a,deleted b
where a.employeeno=b.employeeno and a.employeeno=@employeeno
end
end
end

(6) 创建触发器，将入职后三个月均没有业绩的业务科人员做离职处理，其信息拷贝到离职表中。
create table lizhi(
employeeNo char(8) not null,
employeeName varchar(10) not null,
hireDate datetime null,
birthday datetime null,
salary numeric(9,2) null,
address varchar(50) null,
telephone varchar(20) null
)
go
create trigger emp_emp
on employee3266
for delete
as
begin
declare @employeeNo char(8),@employeeName varchar(10),@hireDate datetime,@birthday datetime ,@salary numeric(9,2),@address varchar(50),@telephone varchar(20)
if exists(select * from deleted)
begin
select @employeeNo=a.employeeno,@employeeName=b.employeeName,@hireDate=b.hireDate,@birthday=b.birthday,@salary=b.salary,@address=b.address,@telephone=b.telephone
from deleted a,employee3266 b,ordermaster3266 c
where a.employeeno=b.employeeno and b.department !='业务科' and ((year(getdate())=year(b.hiredate) and month(getdate())>month(b.hiredate)+3)or(year(getdate())=year(b.hiredate)+1 and month(getdate())>3-12+month(b.hiredate)))and a.employeeno not in (select saleno from ordermaster3266)
insert into lizhi values(@employeeNo,@employeeName,@hireDate,@birthday,@salary,@address,@telephone)
end
end

7