create trigger yourTriggerName on 附记表
For Insert,Update
As
if exists (Select * from inserted where 编号 not in (select 编号 from 学生表))
begin
raiserror('不符合数据的完整性',16,-1)
rollback transaction
return
end
For Insert,Update
As
if exists (Select * from inserted where 编号 not in (select 编号 from 学生表))
begin
raiserror('不符合数据的完整性',16,-1)
rollback transaction
return
end
FOR delete,update
AS
if update(编号) and exists(select 1 from deleted where 编号 in (select 编号 from 附记表))
rollback tran
FOR delete,update
AS
if exists(select 1 from deleted where 编号 in (select 编号 from 附记表))
rollback tran
gocreate trigger 名2 on 附记表
For Insert,Update
As
if exists (Select 1 from inserted where 编号 not in (select 编号 from 学生表))
rollback tran
go
CREATE TRIGGER 名 ON 学生表
FOR delete,update
AS
if exists(select 1 from deleted where 编号 in (select 编号 from 附记表))
rollback tran
gocreate trigger 名2 on 附记表
For Insert,Update
As
if exists (Select 1 from inserted where 编号 not in (select 编号 from 学生表))
rollback tran
go
for insert,update,deleted --使触发器在新增/修改/删除时起作用
if update(编号) --因为两表之间是用编号关联的,所以触发器仅在编号变动时做处理
begin
if exists(select 1 from inserted)
if exists(select 1 from deleted)
begin
--如果inserted和deleted表中都有记录,则表明为修改操作,则做同步修改
select id=identity(int,1,1),编号 into i from inserted
select id=identity(int,1,1),编号 into d from deleted
update 附记表 set 编号=b.编号
from 附记表 a
,(select i.编号,d.编号 as d编号 from i,d where i.id=d.id) b
where a.编号=b.d编号
end
else
begin
--如果仅inserted表中有记录,则为新增,则做同步新增
insert into 附记表(编号) select 编号 from inserted
end
else
begin
--否则是删除操作,做同步删除
delete from 附记表 where 编号 in(select 编号 from deleted
end
end
go
FOR delete,insert,update
AS
update [memo] set [memo].[id]=[student].[id] where [memo].[id]=[student].[id]
insert [memo] ([memo].[id]) values ([student].[id]) where [student].[id] not in (select [memo].[id] from [memo])
delete * from [memo] where [memo].[id] not in ( select [student].[id] from [student])
select id=identity(int,1,1),编号 into i from inserted
select id=identity(int,1,1),编号 into d from deleted
update 附记表 set 编号=b.编号
from 附记表 a
,(select i.编号,d.编号 as d编号 from i,d where i.id=d.id) b
where a.编号=b.d编号