有三个表
表1 aclxx(adjxh,acph,afdjh,acjh)
表2 fhcxx(fcph,ffdj,fcjh,fbkzt)
表3 qbjcl(qcjxh,qbjsj)当表1插入或更新时(acph,afdjh,acjh) 与表2(fcph,ffdj,fcjh)比对,只要有一个字段符合,并且状态fbkzt=1时。
那么表3 qcjxh=表1 adjxh ,qbjsj=系统时间
表1 aclxx(adjxh,acph,afdjh,acjh)
表2 fhcxx(fcph,ffdj,fcjh,fbkzt)
表3 qbjcl(qcjxh,qbjsj)当表1插入或更新时(acph,afdjh,acjh) 与表2(fcph,ffdj,fcjh)比对,只要有一个字段符合,并且状态fbkzt=1时。
那么表3 qcjxh=表1 adjxh ,qbjsj=系统时间
create trigger tri_g on aclxx
for insert,update
as
begin
select 1 from inserted a , 表2 b where b.fbkzt=1 and ...
if @@rowcount>0
update 表3 set qcjxh=a.adjxh,qbjsj=getdate() from inserted a join on ..
end
FOR INSERT, UPDATE
AS
select a.adjxh from inserted a,fhcxx b where (a.adjxh=b.fcph or a.acph=b.ffdj or a.afdjh=b.fcjh) and b.fbkzt=1
if @@rowcount >0
begin
update qcjxh set qbjsj=getdate() from inserted a,qcjxh b where a.adjxh =b.qcjxh
if @@rowcount=0
insert into qcjxh (qcjxh,qbjsj) select adjxh ,getdate() from inserted end
当表1插入或更新时(acph,afdjh,acjh) 与表2(fcph,ffdj,fcjh)比对
怎么比? 是第一个跟第一个比、第二个跟第二个比(比3次)? 还是随意组合比(比9次)?
acph=fcph 或afdjh=ffdj 或acjh=fcjh