CREATE TABLE [dbo].[TABLE1] (
[bh] [int] NULL ,
[test1] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[test2] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[test3] [char] (1) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
go
CREATE TABLE [dbo].[TABLE2] (
[bh] [int] NULL ,
[test1] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[test2] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[test3] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[result] [char] (1) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
go
create trigger on_insert on table2
for insert
as
declare @test1 char(1),
@test2 char(1),
@test3 char(1),
@bh int,
@result char(1)
select @test1=test1,@test2=test2 ,@test3=test3 from inserted table2
if(@test1=0)
set @result=0
else if(@test2=0 and @test3=0)
set @result=0
else
set @result=1
update table2 set result=@result where bh=@bh
go
-----------------------------------------------------
--测试
-----------------------------------------------------
insert into table2(bh,test1,test2,test3) select * from table1
只能影响最后一条记录,有什么好的解决方法没?
[bh] [int] NULL ,
[test1] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[test2] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[test3] [char] (1) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
go
CREATE TABLE [dbo].[TABLE2] (
[bh] [int] NULL ,
[test1] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[test2] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[test3] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[result] [char] (1) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
go
create trigger on_insert on table2
for insert
as
declare @test1 char(1),
@test2 char(1),
@test3 char(1),
@bh int,
@result char(1)
select @test1=test1,@test2=test2 ,@test3=test3 from inserted table2
if(@test1=0)
set @result=0
else if(@test2=0 and @test3=0)
set @result=0
else
set @result=1
update table2 set result=@result where bh=@bh
go
-----------------------------------------------------
--测试
-----------------------------------------------------
insert into table2(bh,test1,test2,test3) select * from table1
只能影响最后一条记录,有什么好的解决方法没?
create trigger on_insert on table2
for insert
as
update a
set a.result = case when b.test1 = 0 or (b.test2 = 0 and b.test3 = 0) then '0' else 1 end
from table2 a join inserted b on a.bh = b.bh
go
create trigger on_insert on table2
for insert
as
declare @test1 char(1),
@test2 char(1),
@test3 char(1),
@bh int,
@result char(1)
select @bh=bh,@test1=test1,@test2=test2 ,@test3=test3 from inserted table2
if(@test1=0)
set @result=0
else if(@test2=0 and @test3=0)
set @result=0
else
set @result=1
update table2 set result=@result where bh=@bh
go
create trigger on_insert on table2
for insert
as
update a
set a.result = case when b.test1 = 0 or (b.test2 = 0 and b.test3 = 0) then '0' else '1' end
from table2 a join inserted b on a.bh = b.bh
go
for insert
as
update a
set a.result = case when b.test1 = '0' or (b.test2 = '0' and b.test3 = '0') then '0' else '1' end
from table2 a join inserted b on a.bh = b.bh
go
意思是当table2插入数据时,根据test1,test2,test3的值,来修改result的值