下面表[OrderDemo]有一个字段[Num]，此字段作为排序字段，在插入时它的值为主键一样，但不能设为IDENTITY自动增长，因为它有可能被更新为其它值。
代码
IF
OBJECT_ID
(
'
[dbo].[OrderDemo]
'
)
IS
NOT
NULL
DROP TABLE [ dbo ] . [ OrderDemo ]
GO
CREATE TABLE [ dbo ] . [ OrderDemo ]
(
OrderDemoId INT IDENTITY ( 1 , 1 ) PRIMARY KEY NOT NULL ,
[ Name ] NVARCHAR ( 50 ) NOT NULL ,
[ Num ] INT NULL
)
GO
DROP TABLE [ dbo ] . [ OrderDemo ]
GO
CREATE TABLE [ dbo ] . [ OrderDemo ]
(
OrderDemoId INT IDENTITY ( 1 , 1 ) PRIMARY KEY NOT NULL ,
[ Name ] NVARCHAR ( 50 ) NOT NULL ,
[ Num ] INT NULL
)
GO
现在我们需要写一个触发器(AFTER INSERT)，当在记录插入时，把记录自动产生的主键值写入这个字段中。
代码
IF EXISTS ( SELECT [ name ] FROM sysobjects WHERE [ name ] = ' tri_OrderDemo_Insert ' AND TYPE = ' TR ' )
DROP TRIGGER [ dbo ] . [ tri_OrderDemo_Insert ]
GO
CREATE TRIGGER [ dbo ] . [ tri_OrderDemo_Insert ] ON [ dbo ] . [ OrderDemo ]
AFTER INSERT
AS
DECLARE @PK INT
SELECT @PK = [ OrderDemoId ] FROM inserted
UPDATE [ dbo ] . [ OrderDemo ] SET [ Num ] = @PK WHERE [ OrderDemoId ] = @PK