我现在想实现在表三中插入值(value3)时,首先检查该值是否存在于表1的value1或者表2的value2中,并且当表1或表2中值发生改变时,即update or delete 时,表3中的值跟着发生变化.
这样子应该如何来实现
...全文
904打赏收藏
数据表之前的关系问题
表1: ID, value1; 表2: ID value2; 表3: ID value3; 我现在想实现在表三中插入值(value3)时,首先检查该值是否存在于表1的value1或者表2的value2中,并且当表1或表2中值发生改变时,即update or delete 时,表3中的值跟着发生变化. 这样子应该如何来实现
create trigger tr3
on 表3
instead of insert
as
if not exists(select 1
from inserted I
join (
select value1 from 表1
union
select value2 from 表2
)T on I.value3=T.value1
)
begin
insert 表3 select value3 from inserted
end
go
--update,delete触发器(表2的和这个类似)
create trigger tr1
on 表1
for update,delete
as
if not exists(select 1 from inserted)
begin
delete from 表3 where value3 in(select value1 from deleted)
end
else
begin
update 表3
set value3=I.value1
from 表3 A
join deleted D on D.value1=A.value1
join inserted I on D.主键=I.主键
end
go