March 10, 2009 at 3:58 pm
Hi Guys,
I want to track the data changes in a table. I am using trigger for this. The table structure for which I am tracking data changes is like this:
COL1 COL2 COL3
A B 1
B A 2
C A 3
the data for col1 and col2 are static and the value for col3 only changes. So, if the value for col3 changes suppose for above case the value 1 changes to 2 then I need to store that information in a audit table that will also have the value of col1 and col2.
Will you please help?
March 10, 2009 at 4:17 pm
Hi
You can use an INSTEAD OF trigger.
[font="Courier New"]
IF (OBJECT_ID('tab1') IS NOT NULL)
DROP TABLE tab1
CREATE TABLE tab1 (id INT, val INT)
IF (OBJECT_ID('tab1_audit') IS NOT NULL)
DROP TABLE tab1_audit
CREATE TABLE tab1_audit (id INT, old_val INT, new_val INT)
GO
IF (OBJECT_ID('tr_tab1_instead') IS NOT NULL)
DROP TRIGGER tr_tab1_instead
GO
CREATE TRIGGER tr_tab1_instead ON tab1 INSTEAD OF UPDATE
AS
SET NOCOUNT ON
INSERT INTO tab1_audit
SELECT i.id, t.val, i.val
FROM inserted i
JOIN tab1 t ON i.id = t.id
UPDATE t SET t.val = i.val
FROM inserted i
JOIN tab1 t ON i.id = t.id
GO
INSERT INTO tab1 VALUES (1, 2)
UPDATE tab1 SET val = 4
UPDATE tab1 SET val = 5
SELECT * FROM tab1_audit
[/font]
Greets
Flo
March 10, 2009 at 5:49 pm
Hey Flo . Thank you for the response. I am working on it with the idea of yours. I will let you know when done..
March 11, 2009 at 11:00 am
Hi Florian,
It worked . Thank you for your help. Much appreciated.
March 11, 2009 at 11:12 am
Thanks for the feedback!
You're welcome 😉
March 11, 2009 at 11:21 am
You can also use an after trigger (the default type), which is more common for auditing as it adds to the original modification statement instead of replacing it.
The code below would insert the new values into the audit table, you could select from the deleted table to insert the old information.
CREATE TRIGGER tr_tab1_update ON tab1 FOR UPDATE
AS
INSERT INTO tab1_audit (col1, col2, col3)
SELECT i.col1, i.col2, i.col3
FROM inserted i;
GO
March 16, 2009 at 1:49 pm
Matt,
would this trigger work if the update was done on a linked table from Access? I support users touching tables directly, but not writing UPDATE statements. They go into the actual table and make changes. These tables are linked to the actual table in SQL 2005.
-uman
March 16, 2009 at 2:05 pm
I haven't had to touch Access in about 15 years, so ultimately you should test for yourself. I can say that the trigger is part of an atomic transaction in SQL, and changes to the base table will not commit without the trigger firing successfully (assuming it isn't disabled of course). If the changes in Access and the changes in SQL are transactionally consistent then you should be able to use this approach.
March 16, 2009 at 3:02 pm
would this trigger work if the update was done on a linked table from Access? I support users touching tables directly, but not writing UPDATE statements. They go into the actual table and make changes. These tables are linked to the actual table in SQL 2005.
Using MS Access is the same as using SQL Server Mgt Studio (SSMS).
It doesn't matter what causes the record to change (updated thru a connection, MS Access, SSMS, etc.) in SQL Server. Since the trigger is on the table: the row changes, the trigger fires.
Here's a sample; put it in your db connect and watch the results (note the default value of the last column of the archive table).
CREATE TABLE [dbo].[MainTable](
[pk] [int] NOT NULL,
[Field1] [varchar](50) NULL,
[Field2] [varchar](50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[MainTable_Archive](
[AuditId] [int] IDENTITY(1,1) NOT NULL,
[pk] [int] NOT NULL,
[Field1] [varchar](50) NULL,
[Field2] [varchar](50) NULL,
[AuditAction] [char](1) NOT NULL,
[AuditDate] [datetime] NOT NULL CONSTRAINT
[DF_MainTable_Archive_AuditDate]
DEFAULT (getdate()),
[AuditUser] [varchar](50) NOT NULL CONSTRAINT
[DF_MainTable_Archive_AuditUser]
DEFAULT (suser_sname()),
[AuditApp] [varchar](128) NULL CONSTRAINT
[DF_MainTable_Archive_AuditApp]
DEFAULT (('App=('+rtrim(isnull(app_name(),'')))+') '),
CONSTRAINT [PK_MainTable_Archive] PRIMARY KEY CLUSTERED
([AuditId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
[ChrisC: edited above code to wrap lines]
CREATE TRIGGER [dbo].[tr_MainTable_Update] ON [dbo].[MainTable]
FOR UPDATE AS
INSERT INTO MainTable_Archive
([pk], [Field1], [Field2], AuditAction)
SELECT [pk], [Field1], [Field2], 'U'
FROM Inserted
There was a really good sp on this site which will create the archive table and insert+update+delete triggers for you, but I don't remember where to find it (I think it was this site).
HTH,
-Chris C.
March 16, 2009 at 3:14 pm
Take a look at these two articles and the discussions on them. Lots of data on auditing, including some options that may not have occured to you.
http://www.sqlservercentral.com/articles/Auditing/63247/
http://www.sqlservercentral.com/articles/Auditing/63248/
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply