sql server 实验5.2 触发器的建立与使用

来源：互联网发布：dota2起源2多核优化编辑：程序博客网时间：2024/05/17 16:15

一、实验目的

理解触发器的概念和作用；了解触发器的分类及触发条件；掌握触发器的定义及应用。

二、实验原理

1、使用CREATE TRIGGER语句定义触发器，ALTER TRIGGER语句修改触发器，DROP TRIGGER语句删除触发器。

2、触发器分AFTER/FOR和INSTEAD OF两种类型：AFTER/FOR类型的触发器是在相应的触发语句（insert、delete、update）执行完后被触发的。如果触发语句对应的表上有完整性约束，这些完整性约束必须不违背时，相应的触发语句才能执行，然后才能触发对应的AFTER/FOR类型的触发器。INSTEAD OF类型的触发器会在触发语句（insert、delete、update）执行之前被触发，并取代相应的触发语句。

3、在表或视图上，每个INSERT、UPDATE或DELETE语句只能创建一个INSTEAD OF类型的触发器，无法为有外键约束且指定为级联删除或级联修改的表创建DELETE或UPDATE语句上的INSTEAD OF 类型的触发器。

4、 SQL SERVER为每个触发器都创建了两个临时表INSERTED表和DELETED表，这两个表的逻辑结构与被触发器作用的表一样，用户可以读取这两个表的内容，但不能对它们进行修改，触发器执行完后，这两个表也会自动删除。当执行INSERT时，INSERTED表中保存要向表中插入的所有行；当执行DELETE时，DELETED表中保存要从表中删除的所有行；当执行UPDATE时，修改前的行保存在DELETED表中，修改后的行保存在INSERTED行中。

三、实验设备

安装有SQL SERVER 2000的计算机。

四、实验示例

1、写一个允许用户一次只删除一条员工记录的触发器。

createtrigger tr_emp

onemployee for delete as

declare@row_cnt int

select@row_cnt=count(*) from deleted

if@row_cnt>1

begin

print '删除操作不允许删除多条人事表数据!!!'

rollback transaction

end

deletefrom employee

wheresex='女' --显示'删除操作不允许删除多条人事表数据!!!'

2、创建一个触发器，只能接受女员工

createtrigger employee_insert2 on employee

afterinsert

ifexists(select * from inserted wheresex='男')

begin

print '本公司不接受男员工！'

rollback transaction

end

else

print '欢迎加入本公司！'

insertemployee values('E0021','林中正','男','管理','总经理','1974-10-01','1953-05-04',100000,'13879765667','上海市') --显示'本公司不接受男员工！'

3、定义一触发器，保证新添加的员工的工资不能超过5000元

createtrigger employee_insert on employee

afterinsert

if(selectsalary from inserted)>5000

begin

print '新添加的员工的工资不能超过5000元!'

rollback

end

4、在customer表上创建一触发器，用来实现级联删除。

drop trigger del_cus;

create trigger del_cus on customer
instead of delete
as
delete sale_item where order_no in(select order_no from sales where cust_id in(select cust_id from deleted))
delete sales where cust_id in(select cust_id from deleted)
delete customer where cust_id in(select cust_id from deleted)

五、实验内容

1、针对employee表写一个DELETE触发器，显示删除的员工人数。

create trigger emp_delete on employee

for delete

declare @num int

set @num=0

select @num=count(*) from deleted

print '删除的人数为：'+cast(@num as char(10))

delete from employee where sex='男'

drop trigger emp_delete

2、针对employee表写一个UPDATE触发器，限制每次工资额的变动不能超过原工资的20%。

create trigger update_emp on employee

after update

declare @new_salary float,@old_salary float

select @old_salary=salary from deleted

select @new_salary=salary from inserted

ifABS(@new_salary-@old_salary)>@old_salary*0.2

begin

print '每次工资额的变动不能超过原工资的20%'

rollback transaction

end

update employee set salary=salary*0.15

3、定义一个触发器，保证新添加的员工的工资不能超过5000元

create trigger upwy_emp on employee

for insert

if((select salary from inserted)>5000)

begin

print '新添加的员工的工资不能超过5000元'

rollback

end

insert employee values('E0028','杨子','男','业务','职员','1980-12-1','1950-1-1',4600,'07345123596','昆明市')

insert employee values('E0029','陆毅','男','业务','职员','1980-12-2','1950-12-2',6200,'07343120496','上海市')

4、对sale_item表创建一个触发器，当插入一条销售明细记录时，如果该记录的产品数量超过5，则显示“欢迎成为本公司的VIP会员！”

create trigger ins_sait on sale_item

for insert

if((select qty from inserted)>5)

begin

print '欢迎成为本公司的VIP会员！'

rollback

end

insert sale_item values('1004','P0008','6','10000','1996-11-10')

5、针对sales表，定义一触发器保证参照完整性(参照customer表)，不允许向sales表中插入customer表中不存在的客户。

create trigger unin_sales on sales

instead of insert

if((select count(*) from customer wherecust_id in(select cust_id from inserted))<0)

begin

print'不允许向sales表中插入customer表中不存在的客户'

rollback

end

insert sales values(10002,'C0556','E0013',22700.00,'1996-11-10','I000000002')

drop trigger unin_sales

6、针对employee表，定义一触发器用来保证实体完整性，阻止用户修改Employee表中的emp_no列。.

create trigger unaf_emp on employee

for update

declare @emp_id_i char

declare @emp_id_d char

select @emp_id_i=emp_no from inserted

select @emp_id_d=emp_no from deleted

if @emp_id_i<>@emp_id_d

begin

print'不能修改employee表中的emp_no列'

rollback

end

update employee

set emp_no='45645'

where emp_no='E0008'

0 0