例:
INSERT INTO B SELECT ‘0001’,‘1999’,50
当A中有编号为‘0001’,‘1996’时
update a set sl=sl+xxx where bh=xx and ph=xx
当A中没有此编号或是引批号时则:
INSERT INTO A SELECT @BH,@PH,@SL
INSERT INTO B SELECT ‘0001’,‘1999’,50
当A中有编号为‘0001’,‘1996’时
update a set sl=sl+xxx where bh=xx and ph=xx
当A中没有此编号或是引批号时则:
INSERT INTO A SELECT @BH,@PH,@SL
on b
for insert,update,delete
asdeclare @Temp table (
bh char(4) not null,
ph char(6) not null,
sl numeric(8,2) default(0)
)declare @TempSum table (
bh char(4) not null,
ph char(6) not null,
sl numeric(8,2) default(0)
)insert @Temp
select bh,ph,jhsl
from inserted
union all
select bh,ph,-jhsl
from deletedinsert @TempSum
select bh,ph,sum(sl)
from @Temp
group by bh,phupdate a
set sl=a.sl+t.sl
from a,@TempSum t
where a.bh=t.bh
and a.ph=t.ph
insert a(bh,ph,sl)
select bh,ph,sl
from @TempSum t
where not exists (
select 1 from a
where bh=t.bh
and ph=t.ph
)go
但对以下两方面是不是有更好的方法:
一、当多用户工作时,就是多个用户同时INSERT INTO B时,,
二、当A表有大量数据时,如一百万行或更多时,
insert a(bh,ph,sl)
select bh,ph,sl
from @TempSum t
where not exists (
select 1 from a /*是SELECT * FROM A 吧,,*/
where bh=t.bh
and ph=t.ph
这会不会是一个很耗时的操作,因为要遍历A表!
二、这个问题不用担心,在not exists 的SELECT 里,其实写什么的效率都是一样的!我习惯写个 1,这里写 SELECT 1 、SELECT * SELECT BH都是一样的。
FOR INSERT
AS
DECLARE @val1 varchar(30),@val2 varchar(30),@val3 numeric(8,2)
DECLARE cufeB CURSOR FOR SELECT bh,ph,jhsl from insertedopen cufeB
FETCH NEXT FROM cufeb INTO @val1,@val2,@val3WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (SELECT * FROM a WHERE bh = @val1 AND ph = @val2 ) THEN
UPDATE a SET sl = @val3 WHERE bh = @val1 AND ph = @val2
ELSE
INSERT INTO a SELECT @val1,@val2,@val3 FETCH NEXT FROM cufeb INTO @val1,@val2
ENDCLOSE cufeB
DEALLOCATE cufeB
: Yang_(扬帆破浪) 的好,,
FOR inserted
AS
declare @BH CHAR(4)
declare @ph char(6)
declare @sl numeric(8,2)
declare @jhsl numeric(8,2)
declare @count intbegin tran
select @bh = bh ,@ph = ph ,@jhsl = jhsl from insertedselect @count = count(*) from A
where bh = @bh and ph = @phif @count = 0
insert into A
select @bh,@ph,@jhsl
else
begin
select @sl = sl from A where bh = @bh and ph = @ph
update A
set @sl = @jhsl + @sl
where where bh = @bh and ph = @ph
endcommit tran试试看吧
如果是数据量很大的工作表,一是数据的移植,有trigger
做起来很麻烦。二是要保证数据的一致性,trigger吗?实在
不敢相信,还是用同一个事务来处理更安全有效。
至于trigger,用来控制简单的数据操作判断还是可以的。
create trigger ti_b on b
for insert as
update a
set sl = isnull(sl,0) + t1.jhsl
from inserted t1,a
where t1.bh = a.bh and
t1.ph = a.ph
insert into a(bh,ph,sl)
select bh,ph,jhsl
from inserted
where bh not in (select bh from a) or
ph not in (select ph from b)
create trigger ti_b on b
for insert as
update a
set sl = isnull(sl,0) + t1.jhsl
from inserted t1,a
where t1.bh = a.bh and
t1.ph = a.ph
insert into a(bh,ph,sl)
select bh,ph,jhsl
from inserted
where bh not in (select bh from a) or
ph not in (select ph from a)
这句话:
update a
set sl = isnull(sl,0) + t1.jhsl
from inserted t1,a
where t1.bh = a.bh and
t1.ph = a.ph
如果bh和ph以前在 a 中不存在,会不会出现置null值的现象, 据我所知,isnull()只能管到有记录但本字段为空的情况,
“如果bh和ph以前在 a 中不存在,会不会出现置null值的现象”:不存在,如何会出现在更新列表中呢?对谁置空?isnull()正是对出现数据为空时的处理,保险点,实际上如果有缺省值时可省略。