MS SQL UPDATE触发器 [英] MS SQL UPDATE Trigger
问题描述
我正在尝试创建一个触发器,可以在更新触发器发生时显示旧数据和新数据。这是可能的吗?
下面是一个表(City)上的三个触发器的示例,它将记录到单独的表(CityTransactionLog)对表的任何更改。请注意,City表包含IDENTITY(1,1)列(ID),以便Deleted表中的行可以与Trigger for Update事务中INSERTED表中的行匹配。
被跟踪的表格(插入,更新,删除)
< span class =code-keyword> CREATE TABLE [dbo]。[City](
[City] [ varchar ]( 40 ) NOT NULL ,
[状态] [ char ]( 2 ) NOT NULL ,
[ID] [ int ] IDENTITY ( 1 , 1 ) NOT NULL
)ON [ PRIMARY ] 触发器更新的表 - 日志City Table的所有更改
CREATE TABLE [dbo]。[CityTransactionLog](
[时间戳] [ datetime ] NOT NULL ,
[TransactionType] [ varchar ]( 10 ) NOT NULL ,
[ID] [ int ] NOT NULL ,
[Old_City] [ varchar ]( 40 ) NULL ,
[Old_State] [ char ]( 2 ) NULL ,
[New_City] [ varchar ]( 40 ) NULL ,
[New_State] [ char ]( 2 ) NULL
) ON [ PRIMARY ]三个触发器
创建TRIGGER CityTableMaintenanceInsert On City
插入
AS
INSERT INTO CityTransactionLog(Timestamp,ID,TransactionType,New_City,New_State )从Inserted中选择GetDate(),Inserted.ID,'Insert',Inserted.City,Inserted.State;
转到
CREATE TRIGGER CityTableMaintenanceUpdate在城市
之后更新
AS
INSERT INTO CityTransactionLog(时间戳,ID,TRANSACTIONTYPE,Old_City,OLD_STATE,New_City,NEW_STATE)选择GETDATE( ),Deleted.ID,'Update',Deleted.City,Deleted.State,Inserted.City,Inserted.State from Deleted inner join Inserted on Deleted.ID = Inserted.ID;
转到
CREATE TRIGGER CityTableMaintenanceDelete在城市
之后删除
AS
INSERT INTO CityTransactionLog(时间戳,ID,TRANSACTIONTYPE,Old_City,OLD_STATE)选择GETDATE(),删除。 ID,'删除',Deleted.City,Deleted.State from Deleted;
Go
测试结果 - 在同一行上插入,更新和删除后的CityTransactionLog表在City表的
时间戳&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; TRANSACTIONTYPE&NBSP; ID&NBSP; Old_City&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; OLD_STATE&NBSP;&NBSP; New_City&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; NEW_STATE
2013-11-12 08:45:37.010 插入 135 NULL , b $ b 2013-11-12 08:45:49.527 更新 135 Test City MN&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;测试&NBSP;市&NBSP;改为&NBSP;&NBSP;&NBSP; MO
二零一三年十一月十二日&NBSP; 08:45:56.553&NBSP;删除&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 135&NBSP;测试&NBSP;市&NBSP;改为&NBSP; &NBSP; MO&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; NULL&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP; NULL
I'm trying to create a trigger that can show the old data and new data when update trigger occurs. Is this possible?
Here is an example of three triggers on a table (City) that will log to a separate table (CityTransactionLog) any changes to the table. Note that the City table contains an IDENTITY(1,1) column (ID) so that rows from the Deleted table can be matched to rows from the INSERTED table in the Trigger for Update transactions.
The table that is tracked (Insert, Update, Delete)
CREATE TABLE [dbo].[City]( [City] [varchar](40) NOT NULL, [State] [char](2) NOT NULL, [ID] [int] IDENTITY(1,1) NOT NULL ) ON [PRIMARY]The table that the triggers update - A log of all of the changes to the City Table
CREATE TABLE [dbo].[CityTransactionLog]( [Timestamp] [datetime] NOT NULL, [TransactionType] [varchar](10) NOT NULL, [ID] [int] NOT NULL, [Old_City] [varchar](40) NULL, [Old_State] [char](2) NULL, [New_City] [varchar](40) NULL, [New_State] [char](2) NULL ) ON [PRIMARY]The Three Triggers
Create TRIGGER CityTableMaintenanceInsert On City AFTER Insert AS INSERT INTO CityTransactionLog (Timestamp,ID,TransactionType,New_City,New_State) Select GetDate(),Inserted.ID,'Insert',Inserted.City,Inserted.State from Inserted; Go CREATE TRIGGER CityTableMaintenanceUpdate On City AFTER Update AS INSERT INTO CityTransactionLog (Timestamp,ID,TransactionType,Old_City,Old_State,New_City,New_State) Select GetDate(),Deleted.ID,'Update',Deleted.City,Deleted.State,Inserted.City,Inserted.State from Deleted inner join Inserted on Deleted.ID=Inserted.ID; Go CREATE TRIGGER CityTableMaintenanceDelete On City AFTER Delete AS INSERT INTO CityTransactionLog (Timestamp,ID,TransactionType,Old_City,Old_State) Select GetDate(),Deleted.ID,'Delete',Deleted.City,Deleted.State from Deleted; Go
Results of a test - The CityTransactionLog Table after an Insert, Update and Delete on the same row in the City table
Timestamp TransactionType ID Old_City Old_State New_City New_State
2013-11-12 08:45:37.010 Insert 135 NULL NULL Test City MN
2013-11-12 08:45:49.527 Update 135 Test City MN Test City Changed MO
2013-11-12 08:45:56.553 Delete 135 Test City Changed MO NULL NULL
这篇关于MS SQL UPDATE触发器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!