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表的

时间戳           TRANSACTIONTYPE  ID  Old_City              OLD_STATE   New_City             NEW_STATE

2013-11-12  08：45：37.010 插入      135  NULL             ，   b $ b 2013-11-12  08：45：49.527 更新      135  Test  City           MN         测试 市 改为    MO

二零一三年十一月十二日  08：45：56.553 删除      135 测试 市 改为    MO          NULL                  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屋！