有二个表,tableA 和 tableB,字段如下。
tableA
TAID TANAME TAPCS TAPCS1 TAPCS2
001 A001 100 10 2
002 200 10 2
003 B001 150 50 1
004 A002 120 10 3
005 80 10 3
006 90 0 1
007 C001 300 0 5
008 C001 200 0 2
……
……
当在tableB中插入或修改时,当TANAME=TBNAME时,执行触发器将tableA 中的 TANAME 列相同的(TAPCS+TAPCS1)*TAPCS2进行并 回写到tableB中的TBPCS,
当tableB中的TBNAME不存在于tableA 中的TANAME 列时,不执行触发器或回写0或NULL
完成后的效果如下:
tableB
TBID TBNAME TBPCS
B001 A001 220
B002 R003 0或NULL
B003 C001 1500
B004 G002 0或NULL
B005 A002 390
……
…… 以上触发器请教了很多人,当在tableB中插入或修改时,TANAME=TBNAME时,这个没问题,但是当tableB中的TBNAME不存在于tableA 中的TANAME 列中时,报错:“Record changed by another user”
tableA
TAID TANAME TAPCS TAPCS1 TAPCS2
001 A001 100 10 2
002 200 10 2
003 B001 150 50 1
004 A002 120 10 3
005 80 10 3
006 90 0 1
007 C001 300 0 5
008 C001 200 0 2
……
……
当在tableB中插入或修改时,当TANAME=TBNAME时,执行触发器将tableA 中的 TANAME 列相同的(TAPCS+TAPCS1)*TAPCS2进行并 回写到tableB中的TBPCS,
当tableB中的TBNAME不存在于tableA 中的TANAME 列时,不执行触发器或回写0或NULL
完成后的效果如下:
tableB
TBID TBNAME TBPCS
B001 A001 220
B002 R003 0或NULL
B003 C001 1500
B004 G002 0或NULL
B005 A002 390
……
…… 以上触发器请教了很多人,当在tableB中插入或修改时,TANAME=TBNAME时,这个没问题,但是当tableB中的TBNAME不存在于tableA 中的TANAME 列中时,报错:“Record changed by another user”
-- Author: Ken Wong
-- Create date: 2009-12-23 13:52:22
-- Description:
/*====================================================*/
--> 测试数据:[tableA]
if object_id('[tableA]') is not null drop table [tableA]
create table [tableA]([TAID] varchar(3),[TANAME] varchar(4),[TAPCS] int,[TAPCS1] int,[TAPCS2] int)
insert [tableA]
select '001','A001',100,10,2 union all
select '002','200',10,2,null union all
select '003','B001',150,50,1 union all
select '004','A002',120,10,3 union all
select '005','80',10,3,null union all
select '006','90',0,1,null union all
select '007','C001',300,0,5 union all
select '008','C001',200,0,2
--> 测试数据:[tableB]
if object_id('[tableB]') is not null drop table [tableB]
create table [tableB]([TBID] varchar(4),[TBNAME] varchar(4),[TBPCS] int)
insert [tableB]
select 'B001','A001',0 union all
select 'B002','R003',0 union all
select 'B003','C001',0 union all
select 'B004','G002',0 union all
select 'B005','A002',0--创建触发器
create trigger changevalues on [tableB]
for insert,update
as
begin
if EXISTS(select 1 from inserted) and EXISTS(select 1 from deleted)
begin
if EXISTS(select 1 from tableA where [TANAME] = (select [TBNAME] from inserted))
begin
update [tableB] set [TBPCS]=(select (TAPCS+TAPCS1)*TAPCS2 from [tableA] where
[TANAME] = (select [TBNAME] from inserted))
where [TBID] = (select [TBID] from deleted)
end
end
if EXISTS(select 1 from inserted) and not EXISTS(select 1 from deleted)
begin
if EXISTS(select 1 from tableA where [TANAME] = (select [TBNAME] from inserted))
begin
update [tableB] set [TBPCS]=(select (TAPCS+TAPCS1)*TAPCS2 from [tableA] where
[TANAME] = (select [TBNAME] from inserted))
where [TBID] = (select [TBID] from inserted)
end
end
end insert into [tableB]
select 'B006','A001',0
select * from [tableB]
-----------------------------
B001 A001 0
B002 R003 0
B003 A001 220
B004 G002 0
B005 A002 0
B006 A001 220
if object_id('[tableA]') is not null drop table [tableA]
create table [tableA]([TAID] varchar(3),[TANAME] varchar(4),[TAPCS] int,[TAPCS1] int,[TAPCS2] int)
insert [tableA]
select '001','A001',100,10,2 union all
select '002','200',10,2,null union all
select '003','B001',150,50,1 union all
select '004','A002',120,10,3 union all
select '005','80',10,3,null union all
select '006','90',0,1,null union all
select '007','C001',300,0,5 union all
select '008','C001',200,0,2
--> 测试数据:[tableB]
if object_id('[tableB]') is not null drop table [tableB]
create table [tableB]([TBID] varchar(4),[TBNAME] varchar(4),[TBPCS] int)
insert [tableB]
select 'B001','A001',0 union all
select 'B002','R003',0 union all
select 'B003','C001',0 union all
select 'B004','G002',0 union all
select 'B005','A002',0--创建触发器
CREATE trigger changevalues on [tableB]
for insert,update
as
begin
update B set B.[TBPCS]=(A.TAPCS+A.TAPCS1)*A.TAPCS2
from [tableB] B INNER JOIN [tableA] A ON
A.[TANAME] = B.[TBNAME]
end insert into [tableB]
select 'B006','A001',0select * from [tableB]
提示这个。
alter trigger changevalues on [tableB]
for insert,update
as
begin
if EXISTS(select 1 from inserted) and EXISTS(select 1 from deleted)
begin
if EXISTS(select 1 from tableA where [TANAME] in (select [TBNAME] from inserted))
begin
update [tableB] set [TBPCS]=(select (TAPCS+TAPCS1)*TAPCS2 from [tableA] where
[TANAME] = (select distinct [TBNAME] from inserted))
where [TBID] in (select [TBID] from deleted)
end
end
if EXISTS(select 1 from inserted) and not EXISTS(select 1 from deleted)
begin
if EXISTS(select 1 from tableA where [TANAME] = (select [TBNAME] from inserted))
begin
update [tableB] set [TBPCS]=(select (TAPCS+TAPCS1)*TAPCS2 from [tableA] where
[TANAME] = (select [TBNAME] from inserted))
where [TBID] = (select [TBID] from inserted)
end
end
end
create trigger tritableb on tableb
for insert,update
as
update tableb set tbpcs=(tablea.tapcs+tablea.tapcs1)*tablea.tapcs2 from tablea where tableb.tbname=tablea.taname当招行:insert into tableB select 'B006','J001',0
没有问题。请楼主试试。tablea的值:
001 A001 100 10 2
002 200 10 2
003 B001 150 50 1
004 A002 120 10 3
005 80 10 3
006 90 0 1
007 C001 300 0 5
008 C001 200 0 2
009 D001 250 20 8结果:
tableb:
B001 A001 220
B002 R003 0
B003 C001 400
B004 G002 0
B005 A002 390
B006 D001 2160
J006 D001 2160
B006 J001 0
B006 C001 400