|
--针对bookshop数据库，完成以下操作：
--1、针对employee表写一个DELETE触发器，显示删除的员工人数。
create trigger employee_delete?
on employee
for delete
as
? ?select count(*) from deleted
--测试
delete?
from employee
where emp_no not in (select sale_id from sales)
select * from employee
--2、针对employee表写一个UPDATE触发器，限制每次工资额的变动不能超过原工资的20%。
go
create trigger upda_emp on employee
after update
as
if exists(
?? ?select * from inserted a,deleted b
?? ?where a.emp_no=b.emp_no?
?? ?and abs(a.salary-b.salary)>0.2*b.salary )
begin
print'员工工资变动不能超过20%'
rollback
end
--测试代码
select *from employee
update employee
set salary=1.4*salary
where ?emp_no='E0001'
update employee
set salary=1.4*salary
update employee
set salary=1.1*salary
where ?emp_no='E0001'
update employee
set salary=1.1*salary
--3、定义一个触发器，保证新添加的员工的工资不能超过5000元
go
create trigger employee_insert on employee
for insert
as
? if exists(select * from inserted where salary>5000) --新添加的员工的工资超过5000
? begin
? ? ?rollback
?? ? print ?'新增员工工资不能超过5000'
? end
--测试
select * from employee
insert employee values('E0001','王大华','男','业务','经理','1976-10-13','1951-08-01',4000,'13107912134','上海市')
insert employee values('E0001','王大华','男','业务','经理','1976-10-13','1951-08-01',80000,'13107912134','上海市')
--4、对sale_item表创建一个触发器，当插入一条销售明细记录时，如果该记录的产品数量超过5，则显示“欢迎成为本公司的VIP会员！”
go
create trigger sale_item_insert on sale_item
for insert
as
? ?declare @cnt int
? ?select @cnt=qty from inserted
? ?if (@cnt>5) print '欢迎成为本公司的VIP会员！'
--测试
insert into sale_item values('102898','43748 ',6,100)
--5、定义一个触发器，当修改sale_item表中的销售数量或者价格时，自动更新sales表中的订单金额。
go
create trigger sale_item_upd on sale_item
for update
as
if update(qty) or update(unit_price) --如果修改了销售数量或者价格
? ?begin
? ? ? update sales
?? ? ?set total_amt=(select sum(qty*unit_price)?
?? ? ? ? ? ? ? ? ? ? from sale_item?
?? ??? ??? ??? ??? ? where sale_item.order_no=sales.order_no)
? ?end
select order_no, total_amt from sales
select * from sale_item
update sale_item
set qty=2
where order_no='102893' and book_no='20652'
select order_no,sum(qty*unit_price)?
from sale_item ?
group by order_no
--6、定义一个触发器，实现对sales表的级联删除操作。
go
create trigger sales_del ?on sales
instead of delete
as
?--实现级联删除
? begin
? ? delete sale_item where order_no in(select order_no from deleted)
? ? delete sales where order_no in(select order_no from deleted)
? end
--测试
delete sales
where order_no='102898'
select * from sales
select * from sale_item
--7、定义一个触发器，实现对books表中图书编号修改的的级联更新操作。
--由于sale_item表中book_no列不允许为空，该题无法实现--8、定义一个触发器，实现对books表的级联删除操作。
go
create trigger books_upd ?on books
instead of delete
as
?--实现级联删除
? begin
? ? delete sale_item where book_no in(select book_no from deleted)
? ? delete books where book_no in(select book_no from deleted)
? end
--测试
select * from books
select * from sale_item
delete from books
where book_no='20652'
--课堂练习：对于数据库bookshop，创建一个instead of触发器，实现对员工信息(employee表）的级联删除。
go
create trigger del_emp on employee
instead of delete
as
?? ?delete sale_item?
?? ?where order_no in (
?? ??? ?select order_no
?? ??? ?from sales
?? ??? ?where sale_id in (
?? ??? ??? ??? ?select emp_no
?? ??? ??? ??? ?from deleted
?? ??? ??? ??? ?)
?? ??? ?)
?? ?delete sales?
?? ?where sale_id in (
?? ??? ?select emp_no
?? ??? ?from deleted
?? ??? ?)
?? ?delete employee?
?? ?where emp_no in (
?? ??? ?select emp_no
?? ??? ?from deleted
?? ??? ?)
--附加题：针对employee表，定义一触发器用来保证参照完整性
?
go
create trigger emp_upd_del ?on employee
instead of update
as
if not exists(select * from inserted) --如果做删除，实现级联删除
? begin
? ? delete sale_item where order_no in(select order_no from sales where sale_id in(select emp_no from deleted))
?? ?delete sales where sale_id in(select emp_no from deleted)
? ? delete employee where emp_no in(select emp_no from deleted)
? end
else ? --如果做修改
? begin
? ? if update(emp_no)--如果修改了主健值，则实现级联修改
? ? ? ?begin
? ? ? ? ? update sales set sale_id=(select emp_no from inserted) where sale_id in(select emp_no from deleted)
? ? ? ? ? update employee set emp_no=(select emp_no from inserted) where emp_no in(select emp_no from deleted)
? ? ? end
? end
|