create table #A (id int identity(1,1), copyid as id, ---關鍵 col nvarchar(10))insert #A(col) select '9' union all select '9' union all select '54' union all select '78' union all select '321' select * from #Aid copyid col ----------- ----------- ---------- 1 1 9 2 2 9 3 3 54 4 4 78 5 5 321(5 row(s) affected)
使用了一下 ,这个确实是复制了 但是copyid 这个字段就不能修改了
这样你只能使用触发器了或者直接使用语句 update #A set copyid=id where copyid<>id
CREATE TRIGGER [tb_insert] ON tb FOR INSERT ASupdate tb set copyid=@@IDENTITY from inserted where tb.id=inserted.id
create table TestA (id int identity(1,1), copyid int, col nvarchar(10))create trigger UpdateCopyid on TestA for insert as begin update TestA set copyid=i.id from inserted as i where i.id=TestA.id endinsert TestA(copyid,col) select 1,'fdsf' union all select 1,'45' union all select 1,'321' union all select 1,'ghjhj' union all select 1,'fdsf'select * from TestAid copyid col ----------- ----------- ---------- 1 1 fdsf 2 2 45 3 3 321 4 4 ghjhj 5 5 fdsf(5 row(s) affected)
错误信息 @@ERROR = 0 事务在触发器中结束。批处理已中止。
CREATE TRIGGER [tb_insert] ON tb FOR INSERT ASupdate tb set copyid=@@IDENTITY from inserted where tb.id=inserted.iderror: print '错误信息' + str(@@ERROR) rollback transaction */
CREATE TRIGGER [tb_insert] ON tb FOR INSERT ASupdate tb set copyid=inserted.id from inserted where tb.id=inserted.id
返回最后插入的标识值。insert tb (mid) select @@IDENTITY
设为自增了后 能够用 update 修改吗?
一个表不能有2个identity字段
create table #A
(id int identity(1,1),
copyid as id, ---關鍵
col nvarchar(10))insert #A(col)
select '9' union all
select '9' union all
select '54' union all
select '78' union all
select '321' select * from #Aid copyid col
----------- ----------- ----------
1 1 9
2 2 9
3 3 54
4 4 78
5 5 321(5 row(s) affected)
update #A set copyid=id where copyid<>id
FOR INSERT
ASupdate tb set copyid=@@IDENTITY
from inserted where tb.id=inserted.id
create table TestA
(id int identity(1,1),
copyid int,
col nvarchar(10))create trigger UpdateCopyid on TestA for insert
as
begin
update TestA set copyid=i.id from inserted as i where i.id=TestA.id
endinsert TestA(copyid,col)
select 1,'fdsf' union all
select 1,'45' union all
select 1,'321' union all
select 1,'ghjhj' union all
select 1,'fdsf'select * from TestAid copyid col
----------- ----------- ----------
1 1 fdsf
2 2 45
3 3 321
4 4 ghjhj
5 5 fdsf(5 row(s) affected)
事务在触发器中结束。批处理已中止。
FOR INSERT
ASupdate tb set copyid=@@IDENTITY
from inserted where tb.id=inserted.iderror:
print '错误信息' + str(@@ERROR)
rollback transaction */
FOR INSERT
ASupdate tb set copyid=inserted.id
from inserted where tb.id=inserted.id
print '错误信息' + str(@@ERROR)
rollback transaction去掉后 就不错了 这是为什么?
print '错误信息' + str(@@ERROR)
rollback transaction这个代码 去掉后 就不错了
FOR INSERT
AS
begin
update tb set copyid=inserted.id
from inserted where tb.id=inserted.id
end