执行上面修改操作后,我期待A1、A2数据表变成2005A1、2005A2,但是只有A2变成了2005A2
============>
select @old=Deleted.StudentID,@new=Inserted.StudentID from Inserted,Deleted
该语句只取最后一条记录
============>
select @old=Deleted.StudentID,@new=Inserted.StudentID from Inserted,Deleted
该语句只取最后一条记录
====================================================
--当班级ClassID被修改时,更新Student表中的ClassID及StudentID
CREATE TRIGGER [TRIGGER_Class_Update] ON [dbo].[class]
FOR UPDATE
AS
declare @ClassID varchar(40)
update student set ClassID=Inserted.ClassID from Student,Inserted,Deleted where Student.ClassID=Deleted.ClassID
select @ClassID=Inserted.ClassID from Inserted
exec SynTableName @ClassID--所有表同步成功
update student set StudentID=ClassID+SortID--仍然会触发最后一条:(其中存储过程如下:
=================================
CREATE PROCEDURE [dbo].[SynTableName]
@ClassID varchar(255)
AS begin declare @old varchar(40),@new varchar(40)
declare cursorQI cursor for select StudentID,@ClassID+SortID from Student for read only
open cursorQI
while(0=0) begin
Fetch Next from cursorQI into @old,@new
if @@Fetch_Status<>0 break
exec sp_rename @old,@new
end
close cursorQI
Deallocate cursorQI
endGO
--班级,ClassID为编辑编号
CREATE TABLE [dbo].[Class] (
[ClassID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO--学生,其中StudentID=ClassID(班级编码)+SortID(学生在班级的序号)
CREATE TABLE [dbo].[Student] (
[StudentID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[ClassID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[SortID] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO--当班级ClassID被修改时,更新Student表中的ClassID及StudentID
CREATE TRIGGER [TRIGGER_Class_Update] ON [dbo].[Class]
FOR UPDATE
AS
update Student set ClassID=inserted.ClassID from Student,inserted,deleted where Student.ClassID=deleted.ClassID
update Student set StudentID=ClassID+SortIDGO
--当添加了一个新学生时,创建一个以该学生ID为名称的数据表
CREATE TRIGGER [TRIGGER_Insert] ON [dbo].[Student]
FOR INSERT
AS
declare @str varchar (8000)
set @str=''
select @str=@str+' create table ['+ Inserted.StudentID +'](A varchar(10))' from Inserted
exec(@str)
GO
--当学生ID修改时,将以该学生ID为名的数据表进行同步修改
CREATE TRIGGER [TRIGGER_Update] ON [dbo].[Student]
FOR UPDATE
AS
select identity(int,1,1) id,* into #inserted from inserted
select identity(int,1,1) id,* into #deleted from deletedselect * from #inserted
select * from #deleteddeclare @id int
declare @old varchar(255),@new varchar(255)set @id=1while @id<=(select max(id) from #inserted)
begin
select @old=StudentID from #deleted where id=@id
select @new=StudentID from #inserted where id=@id
exec sp_rename @old,@new
print @old
print @new
set @id=@id+1
end
GOinsert into Class values('A')
insert into Student values('A1','A','1')
insert into Student values('A2','A','2')
go
--测试
--sp_msforeachtable 'drop table ?'
exec sp_configure 'nested triggers','1'
reconfigure with override
go
update Class set ClassID='2005A' where ClassID='A'--查看
--select * from Class
--select * from Student
select name from sysobjects where xtype='U'
--删除测试环境
drop table Class,Student--结果
/*
name
-------
Class
Student
2005A1
2005A2
dtproperties(所影响的行数为 5 行)
*/
--班级,ClassID为编辑编号
CREATE TABLE [dbo].[Class] (
[ClassID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO--学生,其中StudentID=ClassID(班级编码)+SortID(学生在班级的序号)
CREATE TABLE [dbo].[Student] (
[StudentID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[ClassID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[SortID] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO--当班级ClassID被修改时,更新Student表中的ClassID及StudentID
CREATE TRIGGER [TRIGGER_Class_Update] ON [dbo].[Class]
FOR UPDATE
AS
update Student set ClassID=inserted.ClassID from Student,inserted,deleted where Student.ClassID=deleted.ClassID
update Student set StudentID=ClassID+SortIDGO--当添加了一个新学生时,创建一个以该学生ID为名称的数据表
CREATE TRIGGER [TRIGGER_Insert] ON [dbo].[Student]
FOR INSERT
AS
declare @str varchar (8000)
set @str=''
select @str=@str+' create table ['+ Inserted.StudentID +'](A varchar(10))' from Inserted
exec(@str)
GO
--当学生ID修改时,将以该学生ID为名的数据表进行同步修改
CREATE TRIGGER [TRIGGER_Update] ON [dbo].[Student]
FOR UPDATE
AS
set nocount on
set ansi_warnings off
select identity(int,1,1) id,* into #inserted from inserted
select identity(int,1,1) id,* into #deleted from deleteddeclare @id int
declare @old varchar(255),@new varchar(255)set @id=1while @id<=(select max(id) from #inserted)
begin
select @old=StudentID from #deleted where id=@id
select @new=StudentID from #inserted where id=@id
exec sp_rename @old,@new
set @id=@id+1
end
set ansi_warnings on
set nocount off
GOinsert into Class values('A')
insert into Student values('A1','A','1')
insert into Student values('A2','A','2')
go
--测试
--sp_msforeachtable 'drop table ?'
exec sp_configure 'nested triggers','1'
reconfigure with override
go
update Class set ClassID='2005A' where ClassID='A'--查看
--select * from Class
--select * from Student
select name from sysobjects where xtype='U'
--删除测试环境
drop table Class,Student--结果
/*
name
-------
Class
Student
2005A1
2005A2
dtproperties(所影响的行数为 5 行)
*/