Click here to Skip to main content
15,903,388 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I'm trying to create a trigger that can show the old data and new data when update trigger occurs. Is this possible?
Posted
Comments
Thanks7872 12-Nov-13 7:32am    
Trigger is not meant for that.
MalwareTrojan 12-Nov-13 7:37am    
You want to Update Trigger??
How is that possible!
Madhu Nair 12-Nov-13 7:39am    
You can insert the old record in to a log table and display the data using queries outside of trigger

1 solution

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)
SQL
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
SQL
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
 
Share this answer
 
v8

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900