create trigger tg on table1
istead of insert
as
begin
insert table1(h1,a1,b1)
select case when h2 is null then 's' else h2 end,
case when a2 is null then 0 else a2 end,
case when b2 is null then 's' else b2 end
from inserted A left join table2 B on B.a2=A.a1 and B.b2=A.b1
end
istead of insert
as
begin
insert table1(h1,a1,b1)
select case when h2 is null then 's' else h2 end,
case when a2 is null then 0 else a2 end,
case when b2 is null then 's' else b2 end
from inserted A left join table2 B on B.a2=A.a1 and B.b2=A.b1
end
for insert
as
declare @h2 varchar(50)
select @h2=h2 from table2 where a2=inserted.a1 and b2=inserted.b1
if @h2 is null
begin
rollback tran
insert into table1 values('s',0,'s')
else
commit tran
for insert, Update
as
if exists(select h2 from table2 a,inserted b where a.a2=b.a1 and a.b2=b.b1)
update table1 set h1=(select h2 from table2 a,inserted b where a.a2=b.a1 and a.b2=b.b1) from inserted where table1.h1=inserted.h1 and table1.a1=inserted.a1 and table1.b1=inserted.b1
else
update table1 set h1='s',a1=0,b1='s' from inserted where table1.h1=inserted.h1 and table1.a1=inserted.a1 and table1.b1=inserted.b1
update table1 set h1=(...) from inserted where ...
_____________ 这个是怎么讲的 似乎去掉才对另外我前边说错一个 table2.b2是int型的 转成varchar后与table1.b1相同
inserted -- 修改后或要新增的数据。
deleted -- 修改前或删除前的数据。