---建立测试环境:create table a (id int,id1 int) insert into a values(1,1230)--第一个问题:如何使table1整个表既不能删除又不能插入记录又不能被修改? create trigger tr1 on a for insert,update,delete as begin rollback tran print'对不起,你不能对这个表进行修改,插入和删除!!' end---测试数据 insert into a values(2,456) update a set id1=78888 where id=1 delete from a where id=1--删除测试环境: drop trigger tr1--第二个问题:如何使table1符合条件的记录不能被修改(如条件为gzdate>='2002-12-12'),但是可以删除记录和添加记录.create trigger tr1 on a for update as if exists(select * from inserted )and exists (select * from deleted) begin if exists(select id1 from deleted where id1=1230) begin rollback tran print'你不能删除id1=1230这个条件的记录' end end---测试数据: update a set id1=2222 where id1=1230--删除测试环境: drop trigger tr1 3、第三个问题:如何使table可以添加记录,可以修改但是不能删除.create trigger tr1 on a for delete as begin rollback tran print '你不能删除本表的任何数据' end--测试数据: delete from a where id=1--删除测试环境: drop trigger tr1
---建立测试环境:create table a (id int,id1 int)
insert into a values(1,1230)--第一个问题:如何使table1整个表既不能删除又不能插入记录又不能被修改?
create trigger tr1
on a
for insert,update,delete
as
begin
rollback tran
print'对不起,你不能对这个表进行修改,插入和删除!!'
end---测试数据
insert into a values(2,456)
update a set id1=78888 where id=1
delete from a where id=1--删除测试环境: drop trigger tr1--第二个问题:如何使table1符合条件的记录不能被修改(如条件为gzdate>='2002-12-12'),但是可以删除记录和添加记录.create trigger tr1
on a
for update
as
if exists(select * from inserted )and exists (select * from deleted)
begin
if exists(select id1 from deleted where id1=1230)
begin
rollback tran
print'你不能删除id1=1230这个条件的记录'
end
end---测试数据:
update a set id1=2222 where id1=1230--删除测试环境: drop trigger tr1
3、第三个问题:如何使table可以添加记录,可以修改但是不能删除.create trigger tr1
on a
for delete
as
begin
rollback tran
print '你不能删除本表的任何数据'
end--测试数据:
delete from a where id=1--删除测试环境: drop trigger tr1