473,851 Members | 2,245 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

audit trail triggers

Hello.

I tried to implement audit trail, by making an audit trail table with the
following fileds:
TableName,Field Name,OldValue,N ewValue,UpdateD ate,type,UserNa me.
Triggers on each table were set to do the job and everything was fine except
that in the audit trail you couldn't know which row exacltly was
updated/inserted/deleted...There fore I introduced 3 additional columnes
(RowMark1, RowMark2, RowMark3) which should identify the
inserted/updated/deleted row.
For example, RowMark1 could be foreign key, RowMark2 could be primary key,
and RowMark3 could be autonumber ID.
But, when I have several rows updated, RowMark columnes values are identical
in all rows in the audit trail table! What is wrong with my code, and how to
solve it ?

Thank you in advance!
CREATE TRIGGER Trigger_audit_T ableName
ON dbo.TableName
FOR DELETE, INSERT, UPDATE
AS BEGIN
declare @type nvarchar(20) ,
@UpdateDate datetime ,
@UserName nvarchar(100),
@RowMark1 nvarchar (100),
@RowMark2 nvarchar (100),
@RowMark3 nvarchar (100)

if exists (select * from inserted) and exists (select * from
deleted)
select @type = 'UPDATE',
@RowMark1=d.For eignKeyField,@R owMark2=d.Prima ryKeyField,@Row Mark3=d.ID
from deleted d
else if exists (select * from inserted)
select @type = 'INSERT',
@RowMark1=i.For eignKeyField,@R owMark2=i.Prima ryKeyField,@Row Mark3=i.ID
from inserted i
else
select @type = 'DELETE',
@RowMark1=d.For eignKeyField,@R owMark2=d.Prima ryKeyField,@Row Mark3=d.ID
from deleted d

select @UpdateDate = getdate() ,
@UserName = USER
/*The following code is repeated for every field in a table*/
if update (FieldName) or @type = 'DELETE'
insert dbo.AUDIT_TRAIL (TableName, FieldName, OldValue, NewValue,
UpdateDate, UserName, type,RowMark1,R owMark2,RowMark 3)
select 'Descriptive Table Name', convert(nvarcha r(100), 'Descriptive
Field Name'),
convert(nvarcha r(1000),d.Field Name),
convert(nvarcha r(1000),i.Field Name),
@UpdateDate, @UserName, @type, @RowMark1, @RowMark2,
@RowMark3
from inserted i
full outer join deleted d
on i.ID = d.ID
where (i.FieldName <> d.FieldName
or (i.FieldName is null and d.FieldName is not null)
or (i.FieldName is not null and d.FieldName is null))

END


Jul 23 '05 #1
3 6299
On Thu, 31 Mar 2005 20:10:46 +0200, Zlatko Matić wrote:
Hello.

I tried to implement audit trail, by making an audit trail table with the
following fileds:
TableName,Fiel dName,OldValue, NewValue,Update Date,type,UserN ame.
Triggers on each table were set to do the job and everything was fine except
that in the audit trail you couldn't know which row exacltly was
updated/inserted/deleted...There fore I introduced 3 additional columnes
(RowMark1, RowMark2, RowMark3) which should identify the
inserted/updated/deleted row.
For example, RowMark1 could be foreign key, RowMark2 could be primary key,
and RowMark3 could be autonumber ID.
But, when I have several rows updated, RowMark columnes values are identical
in all rows in the audit trail table! What is wrong with my code, and how to
solve it ?

Thank you in advance!
Hi Zlatko,

A trigger is fired once per statement execution, not once per row
affected. The changes you made, like for example this one:
select @type = 'UPDATE',
@RowMark1=d.For eignKeyField,@R owMark2=d.Prima ryKeyField,@Row Mark3=d.ID
from deleted d


will set the variables to the values for one of the rows that were
affected by the update. This variable is then used in all inserts to the
audit table!

You should forget the @RowMark1, -2, and -3 variables. Instead, change
the code to insert audit data to something like this:

insert dbo.AUDIT_TRAIL (TableName, FieldName, OldValue, NewValue,
UpdateDate, UserName, type,RowMark1,R owMark2,RowMark 3)
select 'Descriptive Table Name', convert(nvarcha r(100), 'Descriptive
Field Name'),
convert(nvarcha r(1000),d.Field Name),
convert(nvarcha r(1000),i.Field Name),
@UpdateDate, @UserName, @type,
COALESCE (d.ForeignKeyFi eld, i.ForeignKeyFie ld),
COALESCE (d.PrimaryKeyFi eld, i.PrimaryKeyFie ld),
COALESCE (d.ID, i.ID)
from inserted i
full outer join deleted d
on i.ID = d.ID
where (i.FieldName <> d.FieldName
or (i.FieldName is null and d.FieldName is not null)
or (i.FieldName is not null and d.FieldName is null))

I'd like to add that I'm not very fond of your audit table design. I
personally prefer to use several audit tables: one for each table that
needs auditing, with the same columns, plus extra columns such as
DatetimeOfChang e (added as extra column to the primary key) and userid.
This table will receive a complete copy of a row's data whenever it is
changed. But hey - if this design works for you, then by all means use
it.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #2
Zlatko Matić (zl***********@ sb.t-com.hr) writes:
But, when I have several rows updated, RowMark columnes values are
identical in all rows in the audit trail table!
Of course:
@RowMark1=d.Fo reignKeyField,@ RowMark2=d.Prim aryKeyField,@Ro wMark3=d.ID
from deleted d


If deleted contains 15 rows, how would @RowMark1 get more than one value?

Yes, triggers fires once per statement.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
Thanks.

"Hugo Kornelis" <hugo@pe_NO_rFa ct.in_SPAM_fo> je napisao u poruci interesnoj
grupi:9h******* *************** **********@4ax. com...
On Thu, 31 Mar 2005 20:10:46 +0200, Zlatko Matić wrote:
Hello.

I tried to implement audit trail, by making an audit trail table with the
following fileds:
TableName,Fie ldName,OldValue ,NewValue,Updat eDate,type,User Name.
Triggers on each table were set to do the job and everything was fine
except
that in the audit trail you couldn't know which row exacltly was
updated/inserted/deleted...There fore I introduced 3 additional columnes
(RowMark1, RowMark2, RowMark3) which should identify the
inserted/updated/deleted row.
For example, RowMark1 could be foreign key, RowMark2 could be primary key,
and RowMark3 could be autonumber ID.
But, when I have several rows updated, RowMark columnes values are
identical
in all rows in the audit trail table! What is wrong with my code, and how
to
solve it ?

Thank you in advance!


Hi Zlatko,

A trigger is fired once per statement execution, not once per row
affected. The changes you made, like for example this one:
select @type = 'UPDATE',

@RowMark1=d.For eignKeyField,@R owMark2=d.Prima ryKeyField,@Row Mark3=d.ID
from deleted d


will set the variables to the values for one of the rows that were
affected by the update. This variable is then used in all inserts to the
audit table!

You should forget the @RowMark1, -2, and -3 variables. Instead, change
the code to insert audit data to something like this:

insert dbo.AUDIT_TRAIL (TableName, FieldName, OldValue, NewValue,
UpdateDate, UserName, type,RowMark1,R owMark2,RowMark 3)
select 'Descriptive Table Name', convert(nvarcha r(100), 'Descriptive
Field Name'),
convert(nvarcha r(1000),d.Field Name),
convert(nvarcha r(1000),i.Field Name),
@UpdateDate, @UserName, @type,
COALESCE (d.ForeignKeyFi eld, i.ForeignKeyFie ld),
COALESCE (d.PrimaryKeyFi eld, i.PrimaryKeyFie ld),
COALESCE (d.ID, i.ID)
from inserted i
full outer join deleted d
on i.ID = d.ID
where (i.FieldName <> d.FieldName
or (i.FieldName is null and d.FieldName is not null)
or (i.FieldName is not null and d.FieldName is null))

I'd like to add that I'm not very fond of your audit table design. I
personally prefer to use several audit tables: one for each table that
needs auditing, with the same columns, plus extra columns such as
DatetimeOfChang e (added as extra column to the primary key) and userid.
This table will receive a complete copy of a row's data whenever it is
changed. But hey - if this design works for you, then by all means use
it.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
7234
by: Raphael Gluck | last post by:
Hi, Is it possible for one to program one's pages as such that when a database table is updated over the web, via a form, that an e-mail confirmation is sent to a specified address, notifying the update has taken place? If so is there a tutorial out there that is easy to understand and implement? Thanks so much
2
1905
by: Keith | last post by:
Hi I am developing an ASP application which will interact with a SQL database. A requirement of the application is that there is a full audit trail of any modifications to data. I am struggling a bit to get my head round how to do this - just the concept.
2
2277
by: Zlatko Matić | last post by:
I tried to implement triggers for filling audit-trail table on this way. Everything works fine as long as I don't update the primary key field value. When I try to update PK value, an error occures. The code is the following: CREATE TRIGGER NameOfTheTrigger ON dbo.TableName FOR DELETE, INSERT, UPDATE AS BEGIN declare @type varchar(10) ,
13
4997
by: Jim M | last post by:
I've been playing with Allen Browne's audit code and found it very useful. I need to track record insertions, deletions, and edits for several tables. I am planning to replace Access with Microsoft SQL server for my back end, but continue to use Access for the front end. I understand I can create an audit trail of record changes in SQL at the table level, instead of at the form level in Access. I have been playing with Access since the...
6
5855
by: Parag | last post by:
Hello, I have been assigned the task to design the audit trail for the ASP.NET web application. I don't know what the best practices for such audit trails are. Our application one dedicated user name and password to perform the database operations. I need to capture all the operations which are performed on the database. Also I need to able to capture the operations which directly performed on the backend directly using the tools like...
0
1683
by: JimLad | last post by:
Hi, I've been tasked with reviewing the Authentication and Auditing of an application and database. ASP/ASP.NET 1.1 app with SQL Server 2000 database. Separate audit trail database on same server. The system is intranet based and currently uses Basic Authentication on IIS6. The application itself is mostly classic ASP, but has been
0
1392
by: hary08 | last post by:
I have a module copied ftom this site, here it is: Option Compare Database Option Explicit Public Function AuditTrail() On Error GoTo Err_Audit_Trail 'ACC2000: How to Create an Audit Trail of Record Changes in a Form 'http://support.microsoft.com/default.aspx?scid=kb;en-us;197592
3
3788
by: hary08 | last post by:
im doing a database for Hospital Admission, I have a log in form which prompt user for a password. The source of log in is to look for the values in my Table tblEmployees and match user name and password entered.My case now is I have Audit Trail Module which keeps records of modifications and current user, I wanted my audit trail to log the current user based on who has log from based on my Log in Form.please help ty HERES THE CODE FOR ON...
2
3200
by: rockdc1981 | last post by:
I dont it is possible to put this codes together.. I want to prompt the user to save the record and at the same time have a log of audit trail. the codes work out fine separately. Code for Audit Trail Option Compare Database Const cDQ As String = """" Sub AuditTrail(frm As Form, recordid As Control) 'Track changes to data. 'recordid identifies the pk field's corresponding
0
9898
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9748
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11020
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9506
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7907
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7073
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5736
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4549
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4143
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.