JElster
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
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
ASKER
Deleted would be the OLD value ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, deleted is the old value.
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
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
SELECT TOP 1 IDENTITY(INT, 1, 1) AS id, *
INTO RepInfo_Audit
FROM RepInfo
WHERE 1 = 0
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
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.
ASKER
The table does not exist - I dropped it ? ???
thanks
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 :-)
If you need code to compare old and new versions of rows, just let me know :-)
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