nuch数据库实验四触发器

（4）在DingBao数据库中针对PAPER创建插入触发器TR_PAPER_I、删除触发器TR_PAPER_D、修改触发器触发器TR_PAPER_U。
①、对PAPER的插入触发器：插入的报纸记录，单价为负值或为空时，设定为10元

create trigger TR_PAPER_I on PAPER
for insert as
declare @a money
select @a=ppr from inserted
if @a<0 or @a is null
begin
set @a=10
end

触发器测试

insert into paper values('000009','南昌晚报',null);

出现失误重新创建触发器

create trigger paper_insert on paper
for insert as
declare @ippr float ;
declare @ipno int;
select @ippr=ppr, @ipno=pno from inserted
begin
if @ippr<0 or @ippr is NULL
begin
update paper set ppr=10
where paper.pno=@ipno
end
end;

再次测试

insert into paper values('000010','南昌早报',null);

成功

②、对PAPER的删除触发器：要删除的记录，若正被订阅表CP参照时，级联删除订阅表中相关的订阅记录

create trigger paper_delete on PAPER
for delete as
declare @icount int
select @icount=count(*) from deleted ,cp where deleted.pno=cp.pno
if @icount>=1
begin
declare @ipno int
select @ipno=deleted.pno from deleted
delete from cp where cp.pno=@ipno
end;

触发器测试

delete paper where pno='000001';

结果
问过老师后修改如下：

create trigger paper_delete on paper
instead of delete as
declare @icount int
select @icount=count(*) from deleted ,cp where deleted.pno=cp.pno
if @icount>=1
begin
declare @ipno int
select @ipno=deleted.pno from deleted
delete from cp where cp.pno=@ipno
end;

触发器测试

delete paper where pno='000001';

结果如下

③、对PAPER的修改触发器：当把报纸的单价修改为负值或为空时，提示“输入单价不正确！”的信息，并取消修改操作

create trigger paper_uodate on paper
for update as
declare @ippr float
select @ippr=ppr from inserted
if @ippr<0 or @ippr is NUll
begin
raiserror('输入单价不正确!!',16,1)
rollback transaction 
end

（5）对PAPER表作插入、修改、删除的多种操作，关注并记录3种触发器的触发情况。

insert into paper values('000008','江西晚报',-1);
select * from paper;
select * from cp;
delete paper where pno='000001';

update paper set ppr=-10.5 where pno='000001';

（6）创建DDL触发器，通过它能阻止对DingBao数据库表结构的修改或表的删除。

-- 判断名为 trigger_DDL_Table 的数据库级别的触发器是否存在
if exists(select * from sys.triggers where parent_class=0 and name='trigger_DDL_Table')

drop trigger trigger_DDL_Table on database        --删除名为 trigger_DDL_Table 的数据库级别的触发器
go
create trigger trigger_DDL_Table    -- 创建触发器
on database
for drop_table,alter_table        -- 指定触发器事件
as
    
    print '触发器 trigger_DDL_Table 已禁止对表进行DDL的 drop、alter 操作'
    rollback    --对操作进行回滚
go
drop table paper
alter table paper add cc int null default(1)