create trigger rang on ta
for insert
as
declare @user1 char(5),@jf int,@lev int
select @user1 = user1,@jf = jf from inserted
set @lev = case when @jf > 50 then 2 when @jf>100 then 3
if @user1 not in(select user1 from tb) then insert into tb values(@user1,@lev)
else update tb set lev = @lev from inserted,tb where inserted.user1 = tb.user1
for insert
as
declare @user1 char(5),@jf int,@lev int
select @user1 = user1,@jf = jf from inserted
set @lev = case when @jf > 50 then 2 when @jf>100 then 3
if @user1 not in(select user1 from tb) then insert into tb values(@user1,@lev)
else update tb set lev = @lev from inserted,tb where inserted.user1 = tb.user1
for insert
as
declare @user1 char(5),@jf int,@lev int
select @user1 = user1,@jf = jf from insertedset @lev = case when @jf > 50 then 2 when @jf>100 then 3 end
if @user1 not in(select user1 from tb) then
insert into tb values(@user1,@lev)
else
update tb set lev = @lev where user1 = @user1
for insert
as
begin
if not exists (select * from tb A inner join inserted B on A.user1=B.user1)
begin
insert into tb select * from inserted
end
else
begin
update tb set lev = case when inserted.jf>50 then 2 when inserted.jf>100 then 3 else 0 end
from inserted,tb where inserted.user1 = tb.user1
end
end
insert into ta values('e','110')
但是我查看tb中的數據為
a 2
b 1
c 2
e 110
不是我想要的e 3 的結果阿
insert into ta values ('e','200')
我希望tb中獲得的是e 3
而如果我其中有的比如
insert into ta values('a','110')
那麼我希望tb中的 a 2 更新為 a 3
for insert
as
declare @i_count int
begin
select @i_count=count(*) from tb A inner join inserted B on A.user1=B.user1
if @i_count=0
begin
insert into tb select * from inserted
end
else
begin
update tb set lev = case when 50<inserted.jf<=100 then 2 when inserted.jf>100 then 3 else 0 end
from inserted,tb where inserted.user1 = tb.user1
end
end
for insert
as
declare @user1 char(5),@jf int,@lev int
select @user1 = user1,@jf = jf from insertedset @lev = case when @jf > 50 then 2 when @jf>100 then 3 if not exists(select 1 from tb where user1=@user1 )
begin
insert into tb values(@user1,@lev)
end
else
update tb set lev=@lev where user1=@user1
for insert
as
declare @user1 char(5),@jf int,@lev int
select @user1 = user1,@jf = jf from insertedset @lev = case when @jf > 50 then 2 when @jf>100 then 3 else 0 end ------少了這個if not exists(select 1 from tb where user1=@user1 )
begin
insert into tb values(@user1,@lev)
end
else
update tb set lev=@lev where user1=@user1
基本上ok了謝謝上面的兄弟們