今天看了博主winter13292的《insert into 后获得自动插入的id》,文章建议使用SCOPE_IDENTITY()与IDENT_CURRENT(‘表名’)代替@@IDENTITY,瞬间感叹到自己以前的代码水平只是毕业生水平。
但博主winter13292未具体说明哪种情况下不确定,恰好我想到了一种情况,特记录如下:
创建表t_ID1:
CREATE TABLE [dbo].[t_ID1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TName] [nvarchar](50) NULL)
创建表t_ID2:
CREATE TABLE [dbo].[t_ID2](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TName] [nvarchar](50) NULL)
在表t_td1上创建Triger:
CREATE TRIGGER [dbo].[trig_InsertIntoID2]
ON [dbo].[t_ID1]
AFTER INSERT
AS
BEGIN
insert into t_id2(tName)
select tName from inserted
END
在表t_ID1和t_ID2中Insert不相等的记录数。然后执行以下语句:
insert into t_id1(tName)
select '222'
select @@IDENTITY as tid1
select * from t_id1
select * from t_id2
select IDENT_CURRENT('t_id1') as tid1
select IDENT_CURRENT('t_id2') as tid2
结果如下图所示: