Link to home
Start Free TrialLog in
Avatar of JElster
JElsterFlag for United States of America

asked on

Audit Log that shows Original Values and new values

I'm not a SQL Serv. guy.
But I need a trigger that insert a record into a Log that show the Original values and the new Values. A record for each.
Any ideas?
Thanks

Right now I'm using the following:

CREATE TRIGGER audit_log  ON [dbo].[RepInfo]
FOR INSERT, UPDATE, DELETE
AS
INSERT  repinfo_log  SELECT *  from inserted

thanks
Avatar of muzzy2003
muzzy2003

Add another column to your table to indicate deleted or inserted, then change your trigger to this:

CREATE TRIGGER audit_log  ON [dbo].[RepInfo]
FOR INSERT, UPDATE, DELETE
AS
INSERT repinfo_log SELECT *, 'deleted' from deleted
INSERT repinfo_log SELECT *, 'inserted'  from inserted
Avatar of JElster

ASKER

Deleted would be the OLD value ?
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes, deleted is the old value.
Avatar of JElster

ASKER

SELECT IDENTITY(INT, 1, 1) AS id, *
INTO RepInfo_Audit
SELECT TOP 1 *
FROM RepInfo
WHERE 1 = 0

Get an error

Must specify table to select from.

Is mod a new column?

Thanks
D'OH, CORRECTION:


SELECT TOP 1 IDENTITY(INT, 1, 1) AS id, *
INTO RepInfo_Audit
FROM RepInfo
WHERE 1 = 0
Avatar of JElster

ASKER

Server: Msg 8108, Level 16, State 1, Line 1
Cannot add identity column, using the SELECT INTO statement, to table 'RepInfo_Audit', which already has column 'RepID' that inherits the identity property.


???  Thanks
Looks as if the table already exists.  Naturally the CREATE only needs done one, and only if the table doesn't exist yet.  If you want to use the statement above to create the table, delete the existing table first.
Avatar of JElster

ASKER

The table does not exist - I dropped it ?  ???
thanks
It is already inheriting an identity column when it is created from RepInfo - you can't specify another one!
Thanks!

If you need code to compare old and new versions of rows, just let me know :-)