Using trigger for audit_log
-
I need to modify a group of tables in a database from a package that was purchased from a vendor. The tables do not allow for primaryKeys and are not indexed. The system does not have an audit_log on changes made to records.
In the user interface there is only one save and it updates 15+ tables on each save.
What I need to do is create an audit_log for at least 6 of the main tables. I do not have access to the vendor code. And as far as I know the package does not allow for modifications to the code.
I'm assuming that I will need to add triggers to each of the tables FOR INSERT, UPDATE & DELETE.
This is my first time with triggers and if someone could recomend a good reference I would greatly appreciate it.
-
Here is an example of a Trigger I recently got help with here.
You have some choices to make. You can insert all of the information into one table by putting the Trigger on the main table and then joining the other tables that get updated/inserted as well. Or, do each table seperately. [Note: "inserted" and "deleted" are SQL Server tables which contain the New and Old data].
I would search this site for Triggers as well as BOL (Books On Line).
It was also pointed out that for performance, it is probably better to do seperate Triggers for both Insert and Update. Good hunting....
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[DollarsUpdate]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[DollarsUpdate]
GO
CREATE TRIGGER DollarsUpdate
ON dbo.TableName
AFTER UPDATE
AS
IF @@ROWCOUNT = 0 RETURN
SET NOCOUNT ON
IF UPDATE( [Amount])
BEGIN
UPDATE TableName SET
[Dollars] = New.Dollars
FROM TableName
INNER JOIN inserted New ON( TableName.[ID] = New.[ID])
INNER JOIN deleted Old ON( TableName.[ID] = Old.[ID])
INNER JOIN OtherTableName ON( TableName.[OtherID] = Master.[OtherID])
WHERE Old.[Amount] <> New.[Amount]
END
-----------------------------------------------------------------------------------------IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[DollarsInsert]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[DollarsInsert]
GO
CREATE TRIGGER DollarsInsert
ON dbo.TableName
FOR INSERT
AS
SET NOCOUNT ON
BEGIN
UPDATE TableName SET
[Dollars] = New.Dollars
FROM TableName
INNER JOIN inserted New ON( TableName.[ID] = New.[ID])
INNER JOIN OtherTableName ON( TableName.[OtherID] = Master.[OtherID])
END
I wasn't born stupid - I had to study.
-
Triggers sound like the way to go - I'm assuming that you're thinking of creating two or three tables along the lines of:
AuditInsertDelete(AuditInsertID, Date, Action, Table, user, field1, field2, etc)
AuditUpdate(AudotUpdateID, date, table, user, field, beforevalue, aftervalue)
and then inserting records into them whenever something changes?
The ID fields will be primary keys. The date fields are datetimes showing when the action happened. Table = tablename.
Before you go any further, you should check that SQL Server returns useful information about people's usernames. Numerous applications use a generic SQL Server login to perform database access - this means that SQL Server might think that all of your users are called 'AccountsApp' or whatever. If this is the case, your trigger idea just got much less useful, as you never know who made the change.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thanks for the assist. From what I can tell each user has specific rights to view items and update fields. But this is with in the application itself. Since any user can insert a call into the system, they should have insert and update rights. (which is my main concern). The triggers will be tracking new requests (calls) and updates to the status of the call. If a delete happens the application removes all instances of the record. So my trigger on delete will only be removing all references to that call from the audit log.
Thanks again
-
Try running
exec sp_who2
from Query Analyser and see whether the information displayed there identifies your users.
So you do not want to track the fact that a record (or records) have been deleted?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I'd like too just to prove to the company how useless this app is and that I could right something better. But I am a consultant there and really can't do too much.
-
wow! talk about typo's that right sb write
-
I think you are on the right to prove this is bad. Due diligence is often neglected when someone without Technical expertise or experience makes decisions.
Capture this stuff and wait for your moment to strike. IT WILL HAPPEN!!
Good luck.
I wasn't born stupid - I had to study.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply