求解触发器-高手请进
现有一表A 里有字段 Id, CREATORID, SENDTIME, SENDLEVEL, RF_ID
现有一表B 里有字段 Id CREATORID, SENDTIME, SENDLEVEL, SENDSTATE, Template_Id （有10W数据）
要求实现：现在向表A插入数据同时更新B表
两表的关系是：表A中的RF_ID字段里内容来自与表B中的B.Id也就是 B.B.Id =A.RF_ID
ALTER TRIGGER [dbo].[Tri_Insert_Update_Net]
ON A
AFTER INSERT
AS
BEGIN
declare @CREATORID varchar(40),@SENDTIME datetime,@SENDLEVEL smallint,@RF_ID int
select @CREATORID=CREATORID,
@SENDTIME=SENDTIME,
@SENDLEVEL=SENDLEVEL,
@RF_ID=RF_ID
from inserted
update B
set B.CREATORID=@CREATORID,
B.SENDTIME=@SENDTIME,
B.SENDLEVEL=@SENDLEVEL
where B.Id=@RF_ID
END
为何没有用呢
[解决办法]
--> 测试数据：[表A]if object_id('[表A]') is not null drop table [表A]create table [表A]([Id] int,[CREATORID] int,[SENDTIME] datetime,[SENDLEVEL] int,[RF_ID] int)insert [表A]select 131679,3,'2012-03-27 12:46:30.157',2,4928001 union allselect 131680,3,'2012-03-27 12:46:30.157',2,4928002 union allselect 131681,3,'2012-03-27 12:46:30.157',2,4928003 union allselect 131682,3,'2012-03-27 12:46:30.157',2,4928004 union allselect 131683,3,'2012-03-27 12:46:30.157',2,4928005 union allselect 131684,3,'2012-03-27 12:46:30.157',2,4928006--> 测试数据：[表B]if object_id('[表B]') is not null drop table [表B]create table [表B]([Id] int,[CREATORID] int,[SENDTIME] datetime,[SENDLEVEL] int,[SENDSTATE] int,[Template_Id] int)insert [表B]select 4928001,3,'2012-03-26 20:01:30.000',2,0,8 union allselect 4928002,3,'2012-03-26 20:01:30.000',2,0,10 union allselect 4928003,3,'2012-03-26 20:01:30.000',2,0,9 union allselect 4928004,3,'2012-03-26 20:01:30.000',2,0,9 union allselect 4928005,3,'2012-03-26 20:01:30.000',2,0,8 union allselect 4928006,3,'2012-03-26 20:01:30.000',2,0,8goCREATE TRIGGER [dbo].[Tri_Insert_Update_Net]ON [表A]AFTER INSERTAS update [表B]set [表B].CREATORID=I.CREATORID,[表B].[SENDTIME]=I.SENDTIME,[表B].[SENDLEVEL]=I.SENDLEVELFROM INSERTED I WHERE I.RF_ID=[表B].IDinsert [表A]select 131679,3,'2012-03-27 12:46:30.157',2,4928001 union allselect 131680,3,'2012-03-27 12:46:30.157',2,4928002 union allselect 131681,3,'2012-03-27 12:46:30.157',2,4928003 union allselect 131682,3,'2012-03-27 12:46:30.157',2,4928004 union allselect 131683,3,'2012-03-27 12:46:30.157',2,4928005 union allselect 131684,3,'2012-03-27 12:46:30.157',2,4928006select * from [表B]/*Id CREATORID SENDTIME SENDLEVEL SENDSTATE Template_Id4928001 3 2012-03-27 12:46:30.157 2 0 84928002 3 2012-03-27 12:46:30.157 2 0 104928003 3 2012-03-27 12:46:30.157 2 0 94928004 3 2012-03-27 12:46:30.157 2 0 94928005 3 2012-03-27 12:46:30.157 2 0 84928006 3 2012-03-27 12:46:30.157 2 0 8*/这个结果不对吗？
[解决办法]
if object_id('[表A]') is not null drop table [表A]create table [表A]([Id] int,[CREATORID] int,[SENDTIME] datetime,[SENDLEVEL] int,[RF_ID] int)--> 测试数据：[表B]if object_id('[表B]') is not null drop table [表B]create table [表B]([Id] int,[CREATORID] int,[SENDTIME] datetime,[SENDLEVEL] int,[SENDSTATE] int,[Template_Id] int)insert [表B]select 4928001,3,'2012-03-26 20:01:30.000',2,0,8 union allselect 4928002,3,'2012-03-26 20:01:30.000',2,0,10 union allselect 4928003,3,'2012-03-26 20:01:30.000',2,0,9 union allselect 4928004,3,'2012-03-26 20:01:30.000',2,0,9 union allselect 4928005,3,'2012-03-26 20:01:30.000',2,0,8 union allselect 4928006,3,'2012-03-26 20:01:30.000',2,0,8goCREATE TRIGGER [dbo].[Tri_Insert_Update_Net]ON [表A]AFTER INSERTAS if exists(select 1 from inserted) and not exists (select 1 from deleted)beginupdate [表B]set [表B].CREATORID=A.CREATORID,[表B].[SENDTIME]=A.SENDTIME,[表B].[SENDLEVEL]=A.SENDLEVELFROM [表A] A WHERE A.RF_ID=[表B].IDendgoinsert [表A]select 131679,3,'2012-03-27 12:46:30.157',2,4928001 union allselect 131680,3,'2012-03-27 12:46:30.157',2,4928002 union allselect 131681,3,'2012-03-27 12:46:30.157',2,4928003 union allselect 131682,3,'2012-03-27 12:46:30.157',2,4928004 union allselect 131683,3,'2012-03-27 12:46:30.157',2,4928005 union allselect 131684,3,'2012-03-27 12:46:30.157',2,4928006select * from [表B]/*Id CREATORID SENDTIME SENDLEVEL SENDSTATE Template_Id4928001 3 2012-03-27 12:46:30.157 2 0 84928002 3 2012-03-27 12:46:30.157 2 0 104928003 3 2012-03-27 12:46:30.157 2 0 94928004 3 2012-03-27 12:46:30.157 2 0 94928005 3 2012-03-27 12:46:30.157 2 0 84928006 3 2012-03-27 12:46:30.157 2 0 8*/感觉这样应该行，你试一下看看