触发器怎么批量更新

触发器如何批量更新？

/*
表1:T_Test

编号类型
1A
2A
3A
4B
5B
6B

表2:T_Student

编号姓名
1小明
2小强
3小军
4小红
5小玲
6小巧

两个表的编号都为主键唯一的,
写一个更新触发器在T_Test上,当T_Test的编号更新时,T_Student表的编号跟着其更新
批量更新时这个触发器怎么写?(如执行:update T_Test set 编号='00'+编号 where 类型='A')

*/

----建立测试环境(MSSQL2008)
if  exists (select * from sysobjects where id = object_id(N'[T_Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  drop table [T_Test]
GO
CREATE TABLE [dbo].[T_Test](
[编号] [varchar](50) NOT NULL,
[类型] [varchar](50) NULL,
 CONSTRAINT [PK_T_Test] PRIMARY KEY CLUSTERED 
(
[编号] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
insert into T_Test(编号,类型) select '1','A'
insert into T_Test(编号,类型) select '2','A'
insert into T_Test(编号,类型) select '3','A'
insert into T_Test(编号,类型) select '4','B'
insert into T_Test(编号,类型) select '5','B'
insert into T_Test(编号,类型) select '6','B'

go

if  exists (select * from sysobjects where id = object_id(N'[T_Student]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  drop table [T_Student]
GO

CREATE TABLE [dbo].[T_Student](
[编号] [varchar](50) NOT NULL,
[姓名] [varchar](50) NULL,
 CONSTRAINT [PK_T_Student] PRIMARY KEY CLUSTERED 
(
[编号] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
insert into T_Student(编号,姓名) select '1','小明'
insert into T_Student(编号,姓名) select '2','小强'
insert into T_Student(编号,姓名) select '3','小军'
insert into T_Student(编号,姓名) select '4','小红'
insert into T_Student(编号,姓名) select '5','小玲'
insert into T_Student(编号,姓名) select '6','小巧'

GO


----创建TR_Update_T_Student的更新触发器,这个触发器只能一条更新时有效
if(exists (select * from sysobjects where id=object_id(N'[TR_Update_T_Student]') and objectproperty(id,N'IsTrigger')=1))
drop TRIGGER [TR_Update_T_Student]
GO
CREATE TRIGGER [TR_Update_T_Student] ON [dbo].[T_Test]   
FOR UPDATE  
AS  
Begin Transaction
    UPDATE T_student SET T_student.编号=(SELECT top 1 编号 FROM inserted)  
    WHERE T_student.编号 in (SELECT  编号 FROM Deleted)  
Commit Transaction
go
select * from T_Test
select * from T_Student


go
-- update T_Test set 编号='00'+编号 where 类型='A' --执行无效
--T_Student表不能跟着T_test表批量同步更新,TR_Update_T_Student触发器应该怎样写？

[解决办法]

CREATE TRIGGER [dbo].[TR_Update_T_Student] ON [dbo].[T_Test]   
FOR UPDATE  
AS  
Begin Transaction
update a set 编号=c.编号
from T_student a
join (select 编号,row_number() over(order by @@servername) re from deleted) b on a.编号=b.编号 
join (select 编号,row_number() over(order by @@servername) re from inserted) c on b.re=c.re
Commit Transaction

[解决办法]

CREATE TRIGGER [dbo].[TR_Update_T_Student] ON [dbo].[T_Test]   
FOR UPDATE  
AS  
Begin Transaction
create table #inserted(re bigint IDENTITY(1,1),[编号] varchar(50))
create table #deleted(re bigint IDENTITY(1,1),[编号] varchar(50))

insert #inserted ([编号]) select [编号] from inserted 
 
insert #deleted ([编号]) select [编号] from deleted

update a set 编号=c.编号
from T_student a
join #deleted b on a.编号=b.编号 
join #inserted c on b.re=c.re
Commit Transaction

[解决办法]
2各表除了编号字段无其他可关联字段,不能用触发器实现级联更新,
更新T_Test.编号时需分2个语句完成,其where条件一致.

update a
 set a.编号='00'+b.编号
 from T_Student a
 inner join T_Test b on a.编号=b.编号
 where b.类型='A'

update T_Test set 编号='00'+编号 where 类型='A'


-- 结果
select * from T_Test
/*
编号         类型
---------- ----------
001        A
002        A
003        A
4          B
5          B
6          B

(6 row(s) affected)
*/


select * from T_Student
/*
编号         姓名
---------- ----------
001        小明
002        小强
003        小军
4          小红
5          小玲
6          小巧

(6 row(s) affected)
*/

[解决办法]
其实可以直接使用级联删除，在下面的语句中增加了一个外键约束：

--增加了一个约束，就是级联update
alter table T_Student
add constraint tx  foreign key([编号]) references t_test([编号]) on update cascade

通过这个外键约束，就能自动实现update t_test表的编号时，自动update t_student的编号，就可以了：

----建立测试环境(MSSQL2008)
if  exists (select * from sysobjects where id = object_id(N'[T_Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  drop table [T_Test]
GO
CREATE TABLE [dbo].[T_Test](
[编号] [varchar](50) NOT NULL,
[类型] [varchar](50) NULL,
 CONSTRAINT [PK_T_Test] PRIMARY KEY CLUSTERED 
(
[编号] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
insert into T_Test(编号,类型) select '1','A'
insert into T_Test(编号,类型) select '2','A'
insert into T_Test(编号,类型) select '3','A'
insert into T_Test(编号,类型) select '4','B'
insert into T_Test(编号,类型) select '5','B'
insert into T_Test(编号,类型) select '6','B'

go

if  exists (select * from sysobjects where id = object_id(N'[T_Student]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  drop table [T_Student]
GO

 
CREATE TABLE [dbo].[T_Student](
    [编号] [varchar](50) NOT NULL,
    [姓名] [varchar](50) NULL,
 CONSTRAINT [PK_T_Student] PRIMARY KEY CLUSTERED 
(
    [编号] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
insert into T_Student(编号,姓名) select '1','小明'
insert into T_Student(编号,姓名) select '2','小强'
insert into T_Student(编号,姓名) select '3','小军'
insert into T_Student(编号,姓名) select '4','小红'
insert into T_Student(编号,姓名) select '5','小玲'
insert into T_Student(编号,姓名) select '6','小巧'

GO


--增加了一个约束，就是级联update
alter table T_Student
add constraint tx  foreign key([编号]) references t_test([编号]) on update cascade


select * from T_Test
select * from T_Student

go


-- update T_Test set 编号='00'+编号 where 类型='A' --执行无效
--T_Student表不能跟着T_test表批量同步更新,TR_Update_T_Student触发器应该怎样写？

--更新编号
update T_test
set 编号 = '001'
where 编号 = '1'


--发现已修改
select * from t_test where 编号='001'
/*
编号姓名
001小明
*/

--发现也已经修改
select * from t_student where 编号 = '001' 
/*
编号姓名
001小明
*/

查看更多 下一篇