select identity(int ,1,1) as id, a into #t from tb1 go truncate table tb1 go set identity_insert tb1 on go insert tb1(id,a) select id, a from #t drop table #t go set identity_insert tb1 off go select * from tb1 order by id --第二个问题不大明白,tb2是和tb1完全一样的表吗?那只要和上面一样处理或者truncate重新插入就行了 select identity(int ,1,1) as id, a into #t from tb2 go truncate table tb2 go insert tb2(id,a) select id, a from #t drop table #t go select * from tb2 order by id
--问题一的触发器 create trigger t_tb1 on tb1 for delete as select identity(int ,1,1) as id, a into #t from tb1 truncate table tb1 set identity_insert tb1 on insert tb1(id,a) select id, a from #t drop table #t set identity_insert tb1 off --问题二 不是一样的表,但是id有对应关系?tb1会比tb2记录多吗?如果tb2多,那么tb1的一个id要3变2,万一tb2里面有id为2的,那怎么变?
试了一下, create table tb1( [id] int identity(1,1), a varchar(10) )insert into tb1 select 'aaa' union all select 'bbb' union all select 'ccc'delete tb1 where id=2 insert into tb1 select 'ddd' select * from tb1 order by id---这样的结果: id a ----------- ---------- 1 aaa 3 ccc 4 ddd 如何控制,使得结果: id a ----------- ---------- 1 aaa 2 ccc 3 ddd --也就是id如何重新排列?---你的结果会变成: id a ----------- ---------- 1 aaa 2 ddd 3 ccc
可以保证一点的是tb2的记录肯定要比tb1的少
不可能啊,我这里运行就是这样的 id a ----------- ---------- 1 aaa 2 ccc 3 ddd
--如果a字段是唯一的可以那么写: update b set id=a.id from tb1 a , tb2 b where a.a=b.a
create trigger tr_insert on gbook for insertselect identity(int ,1,1) as id, uname,utitle,ubody, udate into #t from gbook truncate table gbook set identity_insert gbook on insert gbook(id,uname,utitle,ubody, udate) select id, uname,utitle,ubody, udate from #t drop table #tupdate b set id=a.id from gbook a , answer b where a.id=b.id这样提示错误 select 附近? 因为我原来有一个 delete的触发器的
少面上了个as,不过现在还是不行。 因为我好象并没触发到 create trigger tr_insert on gbook for insert ??我原来有一个 create trigger tr_delete on gbook for delete 的
create trigger tr_delete on gbook for delete as --原内容 delete b from answer b where exists(select * from deleted where id=b.id) --新加的: select identity(int ,1,1) as id, uname,utitle,ubody, udate into #t from gbook truncate table gbook set identity_insert gbook on insert gbook(id,uname,utitle,ubody, udate) select id, uname,utitle,ubody, udate from #t drop table #t --更新b表的 update b set id=a.id from gbook a , answer b where a.id=b.id go --这样感觉还是执行的上面的那个,新加的没实现呢?
--insert触发器 create trigger tr_insert on gbook for insert as insert tb2(id,a) select id, a from inserted --delete触发器 create trigger t_tb1 on tb1 for delete as select identity(int ,1,1) as id, a into #t from tb1 truncate table tb1 set identity_insert tb1 on insert tb1(id,a) select id, a from #t drop table #t set identity_insert tb1 off update b set id=a.id from tb1 a , tb2 b where a.a=b.a --这样应该就可以了
触发器不稳定?刚一样的操作,总么也是不行,删除掉重新拷贝,居然可以了。 create trigger tr_delete on gbook for delete as delete b from answer b where exists(select * from deleted where id=b.id)select identity(int ,1,1) as id, uname,utitle,ubody, udate into #t from gbook truncate table gbook set identity_insert gbook on insert gbook(id,uname,utitle,ubody, udate) select id, uname,utitle,ubody, udate from #t drop table #tupdate b set id=a.id from gbook a , answer b where a.id=b.id
麻烦介绍一下,200分全是你的了,呵呵。 这个触发器,觉得加上后有点不理解。 我删除时会触发,为什么在我新加记录时,同样也会触发呢? create trigger tr_delete on gbook for delete 难道不是删除时才触发?
--自已做标识列的例子:--创建得到最大id的函数 create function f_getid() returns int as begin declare @id int select @id=max(id) from tb set @id=isnull(@id,0)+1 return(@id) end go--创建表 create table tb(id int default dbo.f_getid() primary key,name varchar(10)) go--创建触发器,在删除表中的记录时,自动更新记录的id(**如果不要此功能,则删除此触发器) create trigger t_delete on tb AFTER delete as declare @id int,@mid int select @mid=min(id),@id=@mid-1 from deleted update tb set id=@id,@id=@id+1 where id>@mid go--插入记录测试 insert into tb(name) values('张三') insert into tb(name) values('张四') insert into tb(name) values('张五') insert into tb(name) values('张六') insert into tb(name) values('张七') insert into tb(name) values('张八') insert into tb(name) values('张九') insert into tb(name) values('张十')--显示插入的结果 select * from tb--删除部分记录 delete from tb where name in('张五','张七','张八','张十')--显示删除后的结果 select * from tb--删除环境 drop table tb drop function f_getid/*--测试结果 id name ----------- ---------- 1 张三 2 张四 3 张五 4 张六 5 张七 6 张八 7 张九 8 张十(所影响的行数为 8 行)id name ----------- ---------- 1 张三 2 张四 3 张六 4 张九(所影响的行数为 4 行) --*/
go
truncate table tb1
go
set identity_insert tb1 on
go
insert tb1(id,a) select id, a from #t
drop table #t
go
set identity_insert tb1 off
go
select * from tb1 order by id
--第二个问题不大明白,tb2是和tb1完全一样的表吗?那只要和上面一样处理或者truncate重新插入就行了
select identity(int ,1,1) as id, a into #t from tb2
go
truncate table tb2
go
insert tb2(id,a) select id, a from #t
drop table #t
go
select * from tb2 order by id
--不是完全一样的表,我上面只是给个例子。
第二个应该比第一个容易点,就是在更新tb1时,同时更新tb2的id,触发器我没写过楼上给的第一个问题的答案我试一下。想写在触发器里或者存储过程里
create trigger t_tb1 on tb1 for delete
as
select identity(int ,1,1) as id, a into #t from tb1
truncate table tb1
set identity_insert tb1 on
insert tb1(id,a) select id, a from #t
drop table #t
set identity_insert tb1 off
--问题二
不是一样的表,但是id有对应关系?tb1会比tb2记录多吗?如果tb2多,那么tb1的一个id要3变2,万一tb2里面有id为2的,那怎么变?
create table tb1(
[id] int identity(1,1),
a varchar(10)
)insert into tb1
select 'aaa' union all
select 'bbb' union all
select 'ccc'delete tb1 where id=2
insert into tb1
select 'ddd' select * from tb1 order by id---这样的结果:
id a
----------- ----------
1 aaa
3 ccc
4 ddd
如何控制,使得结果:
id a
----------- ----------
1 aaa
2 ccc
3 ddd --也就是id如何重新排列?---你的结果会变成:
id a
----------- ----------
1 aaa
2 ddd
3 ccc
id a
----------- ----------
1 aaa
2 ccc
3 ddd
update b set id=a.id from tb1 a , tb2 b where a.a=b.a
for insertselect identity(int ,1,1) as id, uname,utitle,ubody, udate into #t from gbook
truncate table gbook
set identity_insert gbook on
insert gbook(id,uname,utitle,ubody, udate)
select id, uname,utitle,ubody, udate from #t
drop table #tupdate b set id=a.id from gbook a , answer b where a.id=b.id这样提示错误 select 附近?
因为我原来有一个 delete的触发器的
因为我好象并没触发到
create trigger tr_insert on gbook
for insert
??我原来有一个
create trigger tr_delete on gbook
for delete
的
for delete
as
--原内容
delete b from answer b where exists(select * from deleted where id=b.id)
--新加的:
select identity(int ,1,1) as id, uname,utitle,ubody, udate into #t from gbook
truncate table gbook
set identity_insert gbook on
insert gbook(id,uname,utitle,ubody, udate)
select id, uname,utitle,ubody, udate from #t
drop table #t
--更新b表的
update b set id=a.id from gbook a , answer b where a.id=b.id
go --这样感觉还是执行的上面的那个,新加的没实现呢?
create trigger tr_insert on gbook
for insert
as
insert tb2(id,a) select id, a from inserted
--delete触发器
create trigger t_tb1 on tb1 for delete
as
select identity(int ,1,1) as id, a into #t from tb1
truncate table tb1
set identity_insert tb1 on
insert tb1(id,a) select id, a from #t
drop table #t
set identity_insert tb1 off
update b set id=a.id from tb1 a , tb2 b where a.a=b.a
--这样应该就可以了
create trigger tr_delete on gbook
for delete
as
delete b from answer b where exists(select * from deleted where id=b.id)select identity(int ,1,1) as id, uname,utitle,ubody, udate into #t from gbook
truncate table gbook
set identity_insert gbook on
insert gbook(id,uname,utitle,ubody, udate)
select id, uname,utitle,ubody, udate from #t
drop table #tupdate b set id=a.id from gbook a , answer b where a.id=b.id
这个触发器,觉得加上后有点不理解。
我删除时会触发,为什么在我新加记录时,同样也会触发呢?
create trigger tr_delete on gbook
for delete
难道不是删除时才触发?
因为我用的是truncate table,会使得identity值从头开始计算(delete不会使identity重新计算),所以insert新的记录总能保证是记下来的id值
其实整个过程就相当于利用触发器来实现两表同步.只是在同步之前要进行数据重新排序而已
按你所说的,新加记录时,并不会触发,也就是不会执行到truncate table了。
可是为什么却执行了呢。
我最后的触发器,只有一个tr_delete ,也就是我上面贴到的代码。你上面写的那个我到是可以理解了。
2个触发器的那个。可是为啥我就一个触发器,它还能正常啊?我觉得按我写的,删除时正常,而新加记录时就不应该触发的。
是不是这样?中午结帖。叫你先成星,呵呵十分感谢!
2.新增记录的时候不会执行truncate table.但是因为新增的时候表中的id一定是准确的(就是排序的),所以直接insert就可以了.(truncate是在delete的时候执行的)比如表里面id 开始1,2,3 删除2以后,通过触发器变成1,2(因为用的是truncate table,所以@@identity值是2,也就是接下来你插入的时候id就是3),这样insert的时候就不用再排序了
哦,是不是因为delete时,tb1表的id已经更新完毕了,所以我再加新的记录时就.........
是不是这样?中午结帖。叫你先成星,呵呵
是这样的.
我要圆满还早,大家都要努力,呵呵.
为什么还要用identity?
create function f_getid()
returns int
as
begin
declare @id int
select @id=max(id) from tb
set @id=isnull(@id,0)+1
return(@id)
end
go--创建表
create table tb(id int default dbo.f_getid() primary key,name varchar(10))
go--创建触发器,在删除表中的记录时,自动更新记录的id(**如果不要此功能,则删除此触发器)
create trigger t_delete on tb
AFTER delete
as
declare @id int,@mid int
select @mid=min(id),@id=@mid-1 from deleted
update tb set id=@id,@id=@id+1 where id>@mid
go--插入记录测试
insert into tb(name) values('张三')
insert into tb(name) values('张四')
insert into tb(name) values('张五')
insert into tb(name) values('张六')
insert into tb(name) values('张七')
insert into tb(name) values('张八')
insert into tb(name) values('张九')
insert into tb(name) values('张十')--显示插入的结果
select * from tb--删除部分记录
delete from tb where name in('张五','张七','张八','张十')--显示删除后的结果
select * from tb--删除环境
drop table tb
drop function f_getid/*--测试结果
id name
----------- ----------
1 张三
2 张四
3 张五
4 张六
5 张七
6 张八
7 张九
8 张十(所影响的行数为 8 行)id name
----------- ----------
1 张三
2 张四
3 张六
4 张九(所影响的行数为 4 行)
--*/