SQL Server触发器Trigger(2)
下面表[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
