如何创建触发器，限制删除或修改记录

Question

思路：
1、创建一个表，记录不允许修改、删除的ID。
2、创建触发器，然后操作过程中加上ID NOT IN (SELECT ID FROM 第一步中的表)

Answer 1

这个直接使用外键就可以了哈，B表的字段A_ID，引用A表的ID，就可以了

Answer 2

做了一个实验给你：create table a(id int primary key,v varchar(10) )insert into a
values(1,'aa')
create table b(
id int ,a_id int foreign key references a(id),vv varchar(10)
)insert into b
values(2,1,'bb')
go
delete from a where id = 1
/*
消息 547，级别 16，状态 0，第 2 行
DELETE 语句与 REFERENCE 约束"FK__b__a_id__628FA481"冲突。
该冲突发生于数据库"pubs"，表"dbo.b", column 'a_id'。
语句已终止。
*/

Answer 3

如果是触发器，可以这样：if OBJECT_ID('b') is not null
   drop table b
goif OBJECT_ID('a') is not null
   drop table a
go
create table a(id int primary key,v varchar(10) )insert into a
values(1,'aa')
create table b(
id int ,a_id int ,vv varchar(10)
)insert into b
values(2,1,'bb')
go
create trigger dbo.trigger_a
on a
for delete
asif exists(select * 
          from deleted d 
          inner join b
                  on b.a_id= d.id )
   rollback
 
go
delete from a where id = 1
/*
消息 3609，级别 16，状态 1，第 1 行
事务在触发器中结束。批处理已中止。
*/

Answer 4

谢谢各位热心帮忙。
不过，有一点我可能没说清楚。我的意思是如果A表中的ID已经在B表中使用，则A表中该ID的记录就处于只读状态，不允许删除，也不允许修改。不仅是ID字段不允许修改，该记录的任何一个字段都不允许修改（B表的A_ID可以但不一定都来自A表中的ID）。所以我想可能只有用触发器。但我触发器写不好，故求帮忙。

Answer 5

这样吗：if OBJECT_ID('b') is not null
   drop table b
goif OBJECT_ID('a') is not null
   drop table a
go
create table a(id int primary key,v varchar(10) )insert into a
select 1,'aa' union all
select 2,'111'
create table b(
id int ,a_id int ,vv varchar(10)
)insert into b
values(2,1,'bb')
go
create trigger dbo.trigger_a
on a
for delete,update
as
   rollback
go
--不能删除
delete from a where id = 1
/*
消息 3609，级别 16，状态 1，第 1 行
事务在触发器中结束。批处理已中止。
*/
--不能修改
update a
set v = 'xxx'
/*
消息 3609，级别 16，状态 1，第 1 行
事务在触发器中结束。批处理已中止。
*/

Answer 6

create trigger dbo.trigger_a
on a
for delete,update
as
   rollback
go
是整个表处于只读状态。
我的意思是：
假定A表有记录：
1，‘xxx’
2，‘yyy’
B表有记录：
2，‘aaaa'，’bbbb'
则A表的第2条记录不允许修改和删除(2-不允许修改，yyy-也不允许修改)
第1条记录则不限制。

Answer 7

create trigge dbo.a_U_b on dbo.a for update,delete
as
--update,delete
if exists(select 1 from b join deleted a on a.id=b.id)
begin
     rollback tran
     raiserror('不能修改,删除记录',16,1)
     return
end

Answer 8

if OBJECT_ID('b') is not null
   drop table b
goif OBJECT_ID('a') is not null
   drop table a
go
create table a(id int primary key,v varchar(10) )insert into a
select 1,'aa' union all
select 2,'111'
create table b(
id int ,a_id int ,vv varchar(10)
)insert into b
values(2,1,'bb')
go
create trigger dbo.trigger_a
on a
for delete,update
asif exists(select * 
          from deleted d 
          inner join b
                  on b.a_id= d.id )
   rollback
go
--不能删除
delete from a where id = 1
/*
消息 3609，级别 16，状态 1，第 1 行
事务在触发器中结束。批处理已中止。
*/
--不能修改
update a
set v = 'xxx'
where id= 1
/*
消息 3609，级别 16，状态 1，第 1 行
事务在触发器中结束。批处理已中止。
*/
--id 为2的可以修改,可以删除
update a
set v = 'xxx'
where id= 2delete from a where id = 2

Answer 9

create trigge dbo.a_U_b on dbo.a for update,delete
as
--update,delete
if exists(select 1 from b join deleted a on a.id=b.id)
begin
     rollback tran
     raiserror('不能修改,删除记录',16,1)
     return
end

Answer 10

create table A表
(ID int,descr varchar(10))insert into A表
 select 1,'xxx' union all
 select 2,'yyy'create table B表
(A_ID int,b1 varchar(10),b2 varchar(10))insert into B表
 select 2,'aaaa','bbbb'
-- 建触发器
create trigger tr_tablea on A表
for update,delete
as
begin
 if exists(select 1 from inserted a,B表 b where a.ID=b.A_ID)
  or exists(select 1 from deleted a,B表 b where a.ID=b.A_ID)
 begin
   raiserror('ID在B表已使用,不允许修改或删除.',16,1)
   rollback transaction
 end
end
-- 测试删除ID 2
delete from A表 where ID=2/*
Msg 50000, Level 16, State 1, Procedure tr_tablea, Line 9
ID在B表已使用,不允许修改或删除.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/-- 测试修改ID 2
update A表 set descr='aaa' where ID=2/*
Msg 50000, Level 16, State 1, Procedure tr_tablea, Line 9
ID在B表已使用,不允许修改或删除.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/-- 测试修改ID 1
update A表 set descr='aaa' where ID=1/*
(1 row(s) affected)
*/-- 结果
select * from A表/*
ID          descr
----------- ----------
1           aaa
2           yyy(2 row(s) affected)
*/