求解触发器
日期：2014-05-18 浏览次数：20625 次
求解触发器-高手请进
现有一表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 里有字段 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
为何没有用呢
------解决方案--------------------
- SQL code
--> 测试数据：[表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 all
select 131680,3,'2012-03-27 12:46:30.157',2,4928002 union all
select 131681,3,'2012-03-27 12:46:30.157',2,4928003 union all
select 131682,3,'2012-03-27 12:46:30.157',2,4928004 union all
select 131683,3,'2012-03-27 12:46:30.157',2,4928005 union all
select 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 all
select 4928002,3,'2012-03-26 20:01:30.000',2,0,10 union all
select 4928003,3,'2012-03-26 20:01:30.000',2,0,9 union all
select 4928004,3,'2012-03-26 20:01:30.000',2,0,9 union all
select 4928005,3,'2012-03-26 20:01:30.000',2,0,8 union all
select 4928006,3,'2012-03-26 20:01:30.000',2,0,8
go
CREATE TRIGGER [dbo].[Tri_Insert_Update_Net]
ON [表A]
AFTER INSERT
AS
update [表B]
set [表B].CREATORID=I.CREATORID,
[表B].[SENDTIME]=I.SENDTIME,
[表B].[SENDLEVEL]=I.SENDLEVEL
FROM INSERTED I WHERE I.RF_ID=[表B].ID
insert [表A]
select 131679,3,'2012-03-27 12:46:30.157',2,4928001 union all
select 131680,3,'2012-03-27 12:46:30.157',2,4928002 union all
select 131681,3,'2012-03-27 12:46:30.157',2,4928003 union all
select 131682,3,'2012-03-27 12:46:30.157',2,4928004 union all
select 131683,3,'2012-03-27 12:46:30.157',2,4928005 union all
select 131684,3,'2012-03-27 12:46:30.157',2,4928006
select * from [表B]
/*
Id CREATORID SENDTIME SENDLEVEL SENDSTATE Template_Id
4928001 3 2012-03-27 12:46:30.157 2 0 8
4928002 3 2012-03-27 12:46:30.157 2 0 10
4928003 3 2012-03-27 12:46:30.157 2 0 9
4928004 3 2012-03-27 12:46:30.157 2 0 9
4928005 3 2012-03-27 12:46:30.157 2 0 8
4928006 3 2012-03-27 12:46:30.157 2 0 8
*/
这个结果不对吗？
------解决方案--------------------
- SQL code
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 all
select 4928002,3,'2012-03-26 20:01:30.000',2,0,10 union all
select 4928003,3,'2012-03-26 20:01:30.000',2,0,9 union all
select 4928004,3,'2012-03-26 20:01:30.000',2,0,9 union all
select 4928005,3,'2012-03-26 20:01:30.000',2,0,8 union all
select 4928006,3,'2012-03-26 20:01:30.000',2,0,8
go
CREATE TRIGGER [dbo].[Tri_Insert_Update_Net]
ON [表A]
AFTER INSERT
AS
if exists(select 1 from inserted) and not exists (select 1 from deleted)
begin
update
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
