auditting select statement

We have triggers written for insert/update/deletes of data, now there is a
new requirement to audit select statements ran against the database.
I know SQL Profiler shows the select statements ran. I was wondering if
anyone has suggestions on how to somehow use that function(or any other idea
)
and incorparate it into tracking all select statements?Tracey wrote:
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other id
ea)
> and incorparate it into tracking all select statements?
We monitor everything that happens in our production databases, by
running server-side traces 24x7 into rotating trace log files. Easily
done using sp_trace_create, etc., providing you have the disk space to
store the accumulating trace log files.
You get the added benefit of being able to analyze your database
activity to look for poorly performing queries.|||SQL Server does not track this activity. Profiler can see it because it
views all commands going into the database. Some options:
(1) If you deny SELECT access to tables and views, and force all access
through stored procedures, it is trivial to log this.
(2) You can have a trace running all the time that dumps data into trace
table(s).
(3) Or you can look at 3rd party tools (in which case, you won't have to
write all of the reporting over the trace table(s)). For example,
Lumigent's Audit DB or Log Explorer. See
http://www.aspfaq.com/search.asp?q=lumigent
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other
> idea)
> and incorparate it into tracking all select statements?|||Tracey
Take a look at Dejan's example
For example, lets say we want to follow selects on the Customers table of
the Northwind database. Create a trace with only the following settings:
- SP:StmtCompleted and SQL: StmtCompleted events
- EventClass, TextData, ApplicationName and SPID columns
- DatabaseID Equals 6 (DB_ID() of the Northwind database) and
TextData Like select%customers% filters
- Name the trace SelectTrigger and save it to a table with the same
name in the Northwind database.
Start the trace, and create the following trigger using Query Analyzer:
CREATE TRIGGER TraceSelectTrigger ON SelectTrigger
FOR INSERT
AS
EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!',
warning
Now check how trigger works by performing couple of selects:
SELECT TOP 1 *
FROM Customers
SELECT TOP 1 *
FROM Orders
SELECT TOP 1 c.CustomerID
FROM Customers c INNER JOIN Orders o
ON c.CustomerID=o.CustomerID
With Event Viewer, check whether you got two warnings in the Application log
for the 1st and the 3rd queries (the 2nd should be filtered out).
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other
> idea)
> and incorparate it into tracking all select statements?|||Hi Uri,
Seems good.. But is it fool -proof.
what will happen if I query this way.. just for an arguement
SELECT TOP 1 [Customers List].Customer_ID
FROM Orders as [Customers List]
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Uri Dimant" wrote:

> Tracey
> Take a look at Dejan's example
> For example, let’s say we want to follow selects on the Customers table
of
> the Northwind database. Create a trace with only the following settings:
> - SP:StmtCompleted and SQL: StmtCompleted events
> - EventClass, TextData, ApplicationName and SPID columns
> - DatabaseID Equals 6 (DB_ID() of the Northwind database) and
> TextData Like select%customers% filters
> - Name the trace SelectTrigger and save it to a table with the sa
me
> name in the Northwind database.
> Start the trace, and create the following trigger using Query Analyzer:
>
> CREATE TRIGGER TraceSelectTrigger ON SelectTrigger
> FOR INSERT
> AS
> EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!',
> warning
>
> Now check how trigger works by performing couple of selects:
>
> SELECT TOP 1 *
> FROM Customers
> SELECT TOP 1 *
> FROM Orders
> SELECT TOP 1 c.CustomerID
> FROM Customers c INNER JOIN Orders o
> ON c.CustomerID=o.CustomerID
>
> With Event Viewer, check whether you got two warnings in the Application l
og
> for the 1st and the 3rd queries (the 2nd should be filtered out).
>
> "Tracey" <Tracey@.discussions.microsoft.com> wrote in message
> news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
>
>|||Hi
This one will also be logged to the Apllication Viewer, however I agree that
this method is not perfect
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:499B5B18-A216-4732-BF8A-6B295C23D7D1@.microsoft.com...
> Hi Uri,
> Seems good.. But is it fool -proof.
> what will happen if I query this way.. just for an arguement
> SELECT TOP 1 [Customers List].Customer_ID
> FROM Orders as [Customers List]
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Uri Dimant" wrote:
>

Auditing SQL Server?

Are there any suggestions for auditing transactions including 'selects'
without the use of 'triggers'? Triggers are something our client can NOT us
e.
TIA
REM>> Are there any suggestions for auditing transactions including 'selects'[vbcol=seagreen]
Using profiler to trace the t-SQL statements might be feasible for auditing
SELECT statements, but in transaction heavy systems, this can be a potential
performance issue. Third party products like Entegra from Lumigent also has
a trace-based auditing provision for SELECT statements issued against a
database.
Anith|||YOu could
-pass everything though a stored produre to keep track of it.
-Query the logfile with a logfile application
-Run the profiler and let him trace the statements to a table. 8depending
onyour application this will cost mcuh disk space)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"dt09359" <dt09359@.discussions.microsoft.com> schrieb im Newsbeitrag
news:79C5DC37-01A0-4502-8F26-3156B811F03C@.microsoft.com...
> Are there any suggestions for auditing transactions including 'selects'
> without the use of 'triggers'? Triggers are something our client can NOT
> use.
> TIA
> REM

Auditing SQL Server?

Are there any suggestions for auditing transactions including 'selects'
without the use of 'triggers'? Triggers are something our client can NOT use.
TIA
REM
>> Are there any suggestions for auditing transactions including 'selects'[vbcol=seagreen]
Using profiler to trace the t-SQL statements might be feasible for auditing
SELECT statements, but in transaction heavy systems, this can be a potential
performance issue. Third party products like Entegra from Lumigent also has
a trace-based auditing provision for SELECT statements issued against a
database.
Anith
|||YOu could
-pass everything though a stored produre to keep track of it.
-Query the logfile with a logfile application
-Run the profiler and let him trace the statements to a table. 8depending
onyour application this will cost mcuh disk space)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"dt09359" <dt09359@.discussions.microsoft.com> schrieb im Newsbeitrag
news:79C5DC37-01A0-4502-8F26-3156B811F03C@.microsoft.com...
> Are there any suggestions for auditing transactions including 'selects'
> without the use of 'triggers'? Triggers are something our client can NOT
> use.
> TIA
> REM

Auditing SQL Server?

Are there any suggestions for auditing transactions including 'selects'
without the use of 'triggers'? Triggers are something our client can NOT use.
TIA
REM>> Are there any suggestions for auditing transactions including 'selects'
>> without the use of 'triggers'?
Using profiler to trace the t-SQL statements might be feasible for auditing
SELECT statements, but in transaction heavy systems, this can be a potential
performance issue. Third party products like Entegra from Lumigent also has
a trace-based auditing provision for SELECT statements issued against a
database.
--
Anith|||YOu could
-pass everything though a stored produre to keep track of it.
-Query the logfile with a logfile application
-Run the profiler and let him trace the statements to a table. 8depending
onyour application this will cost mcuh disk space)
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"dt09359" <dt09359@.discussions.microsoft.com> schrieb im Newsbeitrag
news:79C5DC37-01A0-4502-8F26-3156B811F03C@.microsoft.com...
> Are there any suggestions for auditing transactions including 'selects'
> without the use of 'triggers'? Triggers are something our client can NOT
> use.
> TIA
> REM

Auditing Select Statements

Hello,
I need to find out if there is a way to audit selects against a certain
table. I know you can use triggers to monitor inserts, updates and
deletes but is there a way to monitor when someone selects specific
data? Thanks!
Rachael
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Go to Lumigents web site... www.lumigent.com... I beleive they have an
auditing tool that does what you wish..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rachael" <rachael_faber@.hotmail.com> wrote in message
news:eu0FLAVVEHA.584@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need to find out if there is a way to audit selects against a certain
> table. I know you can use triggers to monitor inserts, updates and
> deletes but is there a way to monitor when someone selects specific
> data? Thanks!
> Rachael
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Auditing Select Statements

Hello,
I need to find out if there is a way to audit selects against a certain
table. I know you can use triggers to monitor inserts, updates and
deletes but is there a way to monitor when someone selects specific
data? Thanks!
Rachael
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Go to Lumigents web site... www.lumigent.com... I beleive they have an
auditing tool that does what you wish..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rachael" <rachael_faber@.hotmail.com> wrote in message
news:eu0FLAVVEHA.584@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need to find out if there is a way to audit selects against a certain
> table. I know you can use triggers to monitor inserts, updates and
> deletes but is there a way to monitor when someone selects specific
> data? Thanks!
> Rachael
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Auditing Select Statements

Hello,
I need to find out if there is a way to audit selects against a certain
table. I know you can use triggers to monitor inserts, updates and
deletes but is there a way to monitor when someone selects specific
data? Thanks!
Rachael
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Go to Lumigents web site... www.lumigent.com... I beleive they have an
auditing tool that does what you wish..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rachael" <rachael_faber@.hotmail.com> wrote in message
news:eu0FLAVVEHA.584@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need to find out if there is a way to audit selects against a certain
> table. I know you can use triggers to monitor inserts, updates and
> deletes but is there a way to monitor when someone selects specific
> data? Thanks!
> Rachael
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

audit/history without use of triggers?

Hi

I am looking to implement an audit/history table/tables but am looking
at doing this without the use of triggers.
The reason for doing this is that the application is highly
transactional and speed in critical areas is important.

I am worried that triggers would slow things down.

I am more used to other database where by there is a utility to "dump"
the contents of the transaction logs and use this for auditing
purposes. However SQL Server does not have this functionality (unless
there is a sql server tool - 3rd party that I do not know about)

Has anyone implemented something similar? Or used/using a 3rd party
tool that will do this job.

Effectively the clients would like to "look" at what happened - say 15
minutes ago.

thanks
john"John Pan" <panjo03@.hotmail.com> wrote in message
news:dc85ec7c.0311261223.48827012@.posting.google.c om...
> Hi
> I am looking to implement an audit/history table/tables but am looking
> at doing this without the use of triggers.
> The reason for doing this is that the application is highly
> transactional and speed in critical areas is important.
> I am worried that triggers would slow things down.
> I am more used to other database where by there is a utility to "dump"
> the contents of the transaction logs and use this for auditing
> purposes. However SQL Server does not have this functionality (unless
> there is a sql server tool - 3rd party that I do not know about)
> Has anyone implemented something similar? Or used/using a 3rd party
> tool that will do this job.
> Effectively the clients would like to "look" at what happened - say 15
> minutes ago.
> thanks
> john

There's nothing in SQL Server itself, but there is at least one third party
product available which looks like it may meet your requirements:

http://www.lumigent.com/products/entegra/entegra.htm

Simon|||thanks Simon for your reply - I'll have a look at entegra

regards|||John:

Check out these products:

http://www.apexsql.com/index_lognavigator.htm

http://www.lumigent.com/products/le_sql/le_sql.htm

HTH,

BZ

panjo03@.hotmail.com (John Pan) wrote in message news:<dc85ec7c.0311261223.48827012@.posting.google.com>...
> Hi
> I am looking to implement an audit/history table/tables but am looking
> at doing this without the use of triggers.
> The reason for doing this is that the application is highly
> transactional and speed in critical areas is important.
> I am worried that triggers would slow things down.
> I am more used to other database where by there is a utility to "dump"
> the contents of the transaction logs and use this for auditing
> purposes. However SQL Server does not have this functionality (unless
> there is a sql server tool - 3rd party that I do not know about)
> Has anyone implemented something similar? Or used/using a 3rd party
> tool that will do this job.
> Effectively the clients would like to "look" at what happened - say 15
> minutes ago.
> thanks
> john

Audit Triggers SQL 2000

In the past I have used a poor mans audit trigger for my SQL tables. I basically created another identical table with "_Audit" at the end of it with the same fields. I simply did a trigger like this to copy the entire contents of the Row being updated, deleted, or Inserted.

CREATE TRIGGER fileAudit ON [dbo].[Table1]
FOR INSERT, UPDATE, DELETE
AS
insert Table1_audit
select
*
from inserted i
return

This is bad in several ways and I know that, but it was giving me Audit history that was better than nothing. The one thing I hate most about the above approach is that it copies the enitre record set even if only one field was updated or deleted and the fact I have an audit table for every table in the database.

I want to enhance my Audit Trail to include only one Audit_Table. I would like to mimic this approach that is using CLR for SQL 2005. My Only problem is I am forced to use SQL 2000 for legacy application on the database server. I do not have the ability to migrate to SQL 2005 at this time.

http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

All of my Tables have an Auto-increment PK

Could some one point me in the right way to accomplish this.

I recommend to use the following structure;

TableName NVarchar(1000)

OperationDateTime DateTime

Operation smallint (1-Insert, 2-Update, 3-Delete)

Data Text (Common Delimited or XML value of the affected row)

The real challenge here is getting back – querying - the data. For that probably you can design your own UI where you can parse these values for display.

But again these audit trail operations are additional overhead on your data manipulation.

|||

TableName NVarchar(1000)

OperationDateTime DateTime

Operation smallint (1-Insert, 2-Update, 3-Delete)

Data Text (Common Delimited or XML value of the affected row)

Structure should depends on the purposes of audit. Because using this structure it is possible that you could never answer the question who changed some order or what have done John with another orders.
|||

don't use the '*' for auditting or you have to be very carefull of the datatype. you can't read timestamps, text, ... columns from the virtual inserted and deleted tables...

regards

skafever

Audit triggers problem

Im using triggers to track changes Insert/Update/Deletes on my DB tables and they work for when i am manually adding/editing and deleting a single records.

The problem arises in that I have an asset/inventory management app that dumps lots of details into my DB tables at once each time its run.
Not all of the tables are updated and data cannot be completely inserted.

This is the trigger i have been using - could someone tell me how to modify it to work.

/*
This trigger audit trails all changes made to a table.
It will place in the table Audit all inserted, deleted, changed columns in the table on which it is placed.
It will put out an error message if there is no primary key on the table
You will need to change @.TableName to match the table to be audit trailed
*/

ALTER trigger tr_TableName
on dbo.TableName for insert, update, delete
as

declare @.bit int ,
@.field int ,
@.maxfield int ,
@.char int ,
@.fieldname varchar(128) ,
@.TableName varchar(128) ,
@.PKCols varchar(1000) ,
@.sql varchar(2000),
@.UpdateDate varchar(21) ,
@.Action nvarchar(50) ,
@.HostName nvarchar(50),
@.PKFieldName varchar (1000)


IF EXISTS(SELECT * FROM inserted)
IF EXISTS(SELECT * FROM deleted)
--update = inserted and deleted tables both contain data
BEGIN
SET @.Action = 'UPDATE'
SELECT @.DeviceID = (SELECT inserted.DeviceID FROM inserted INNER JOIN deleted ON inserted.deviceID = deleted.deviceid)
END
ELSE

--insert = inserted contains data, deleted does not
BEGIN
SET @.Action = 'INSERT'
select @.DeviceID = (SELECT DeviceID from inserted)
END
ELSE
--delete = deleted contains data, inserted does not
BEGIN
SET @.Action = 'DELETE'
select @.DeviceID = (SELECT DeviceID from deleted)
END

select @.TableName = 'TableName'

-- date
select @.HostName = host_name(),
@.UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114),
--@.DeviceID,
@.PKFieldName=(select top 1 c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = @.TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY' and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)

-- get list of columns
select * into #ins from inserted
select * into #del from deleted

-- Get primary key columns for full outer join
select @.PKCols = coalesce(@.PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @.TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

if @.PKCols is null
begin
raiserror('no PK on table %s', 16, -1, @.TableName)
return
end

select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @.TableName
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @.TableName and ORDINAL_POSITION > @.field
select @.bit = (@.field - 1 )% 8 + 1
select @.bit = power(2,@.bit - 1)
select @.char = ((@.field - 1) / 8) + 1
--if substring(COLUMNS_UPDATED(),@.char, 1) & @.bit > 0
begin
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @.TableName and ORDINAL_POSITION = @.field
select @.sql = 'insert LITE_Inventory (TableName, FieldName, OldValue, NewValue, UpdateDate, Action, Host, PkFieldName, DeviceID)'
-- select @.sql = 'insert LITE_Inventory (TableName, FieldName, OldValue, NewValue, UpdateDate, Action, Host, PkFieldName)'
select @.sql = @.sql + ' select ''' + @.TableName + ''''
select @.sql = @.sql + ',''' + @.fieldname + ''''
select @.sql = @.sql + ',convert(varchar(1000),d.' + @.fieldname + ')'
select @.sql = @.sql + ',convert(varchar(1000),i.' + @.fieldname + ')'
select @.sql = @.sql + ',''' + @.UpdateDate + ''''
select @.sql = @.sql + ',''' + @.Action + ''''
select @.sql = @.sql + ',''' + @.HostName + ''''
select @.sql = @.sql + ',''' + @.PKFieldName + ''''
select @.sql = @.sql + ' from #ins i full outer join #del d'
select @.sql = @.sql + @.PKCols
select @.sql = @.sql + ' where i.' + @.fieldname + ' <> d.' + @.fieldname
select @.sql = @.sql + ' or (i.' + @.fieldname + ' is null and d.' + @.fieldname + ' is not null)'
select @.sql = @.sql + ' or (i.' + @.fieldname + ' is not null and d.' + @.fieldname + ' is null)'

exec (@.sql)
end
endYou have to remember that inerted and deleted can have a set of data...not just 1 row...

Tracking chnages should br pretty straighty forward...

If the data is altered just move the whole row to history...|||You have to remember that inerted and deleted can have a set of data...not just 1 row...

Tracking chnages should br pretty straighty forward...

If the data is altered just move the whole row to history...

How do 'move the whole row to history' - im not sure.

The reason im using this trigger is that it automatically inserts data into a single audit table from all the tables where is has been applied. I can then show this audit table to my users though a GUI.|||USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99 (Col1 int IDENTITY(1,1), Col2 char(1), ModifiedDate datetime)
CREATE TABLE myAudit99 (AuditAddDate datetime DEFAULT GetDate(), Col1 int, Col2 char(1), ModifiedDate datetime)
GO

CREATE TRIGGER myTrigger99 ON myTable99
FOR UPDATE, DELETE
AS
INSERT INTO myAudit99(Col1, Col2, ModifiedDate)
SELECT Col1, Col2, ModifiedDate FROM deleted
GO

INSERT INTO myTable99(Col2)
SELECT 'x'
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO

UPDATE myTable99 SET Col2 = 'A', ModifiedDate = GetDate()
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO

UPDATE myTable99 SET Col2 = 'Z', ModifiedDate = GetDate()
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO

DELETE FROM myTable99 WHERE Col1 = 1
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myAudit99
GO

audit trial

Hi ,
We can do audit trial through triggers in sql server , but oracle itself
having one feature for audit trialing with out using triggers, can any one
suggests me whether its possible in sql server with out using triggers.
With SQL Server tools, you can use Profiler.
There are some 3rd party tools, for example Entegra from Lumigent
(http://www.lumigent.com/).
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"sekhar" <sekhar@.discussions.microsoft.com> wrote in message
news:072C59CD-A1DF-4BF3-BC02-33FFFA0F15CF@.microsoft.com...
> Hi ,
> We can do audit trial through triggers in sql server , but oracle itself
> having one feature for audit trialing with out using triggers, can any one
> suggests me whether its possible in sql server with out using triggers.
|||Hi,
Also see .. C2 Auditing.
C2 Auditing option to review both successful and unsuccessful attempts to
access statements and objects. See books online for
more details.
Thanks
Hari
MCDBA
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:eyNCQ41gEHA.4092@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> With SQL Server tools, you can use Profiler.
> There are some 3rd party tools, for example Entegra from Lumigent
> (http://www.lumigent.com/).
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "sekhar" <sekhar@.discussions.microsoft.com> wrote in message
> news:072C59CD-A1DF-4BF3-BC02-33FFFA0F15CF@.microsoft.com...
one
>

audit trial

Hi ,
We can do audit trial through triggers in sql server , but oracle itself
having one feature for audit trialing with out using triggers, can any one
suggests me whether its possible in sql server with out using triggers.With SQL Server tools, you can use Profiler.
There are some 3rd party tools, for example Entegra from Lumigent
(http://www.lumigent.com/).
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"sekhar" <sekhar@.discussions.microsoft.com> wrote in message
news:072C59CD-A1DF-4BF3-BC02-33FFFA0F15CF@.microsoft.com...
> Hi ,
> We can do audit trial through triggers in sql server , but oracle itself
> having one feature for audit trialing with out using triggers, can any one
> suggests me whether its possible in sql server with out using triggers.|||Hi,
Also see .. C2 Auditing.
C2 Auditing option to review both successful and unsuccessful attempts to
access statements and objects. See books online for
more details.
Thanks
Hari
MCDBA
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:eyNCQ41gEHA.4092@.TK2MSFTNGP10.phx.gbl...
> With SQL Server tools, you can use Profiler.
> There are some 3rd party tools, for example Entegra from Lumigent
> (http://www.lumigent.com/).
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "sekhar" <sekhar@.discussions.microsoft.com> wrote in message
> news:072C59CD-A1DF-4BF3-BC02-33FFFA0F15CF@.microsoft.com...
> > Hi ,
> > We can do audit trial through triggers in sql server , but oracle itself
> > having one feature for audit trialing with out using triggers, can any
one
> > suggests me whether its possible in sql server with out using triggers.
>

audit trial

Hi ,
We can do audit trial through triggers in sql server , but oracle itself
having one feature for audit trialing with out using triggers, can any one
suggests me whether its possible in sql server with out using triggers.With SQL Server tools, you can use Profiler.
There are some 3rd party tools, for example Entegra from Lumigent
(http://www.lumigent.com/).
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"sekhar" <sekhar@.discussions.microsoft.com> wrote in message
news:072C59CD-A1DF-4BF3-BC02-33FFFA0F15CF@.microsoft.com...
> Hi ,
> We can do audit trial through triggers in sql server , but oracle itself
> having one feature for audit trialing with out using triggers, can any one
> suggests me whether its possible in sql server with out using triggers.|||Hi,
Also see .. C2 Auditing.
C2 Auditing option to review both successful and unsuccessful attempts to
access statements and objects. See books online for
more details.
Thanks
Hari
MCDBA
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:eyNCQ41gEHA.4092@.TK2MSFTNGP10.phx.gbl...
> With SQL Server tools, you can use Profiler.
> There are some 3rd party tools, for example Entegra from Lumigent
> (http://www.lumigent.com/).
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "sekhar" <sekhar@.discussions.microsoft.com> wrote in message
> news:072C59CD-A1DF-4BF3-BC02-33FFFA0F15CF@.microsoft.com...
one[vbcol=seagreen]
>

audit trail triggers

Hello.

I tried to implement audit trail, by making an audit trail table with the
following fileds:
TableName,FieldName,OldValue,NewValue,UpdateDate,t ype,UserName.
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...Therefore 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_TableName
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.ForeignKeyField,@.RowMark2=d.PrimaryKey Field,@.RowMark3=d.ID
from deleted d
else if exists (select * from inserted)
select @.type = 'INSERT',
@.RowMark1=i.ForeignKeyField,@.RowMark2=i.PrimaryKey Field,@.RowMark3=i.ID
from inserted i
else
select @.type = 'DELETE',
@.RowMark1=d.ForeignKeyField,@.RowMark2=d.PrimaryKey Field,@.RowMark3=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,RowMark2,RowMark3)
select 'Descriptive Table Name', convert(nvarchar(100), 'Descriptive
Field Name'),
convert(nvarchar(1000),d.FieldName),
convert(nvarchar(1000),i.FieldName),
@.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))

ENDOn 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,FieldName,OldValue,NewValue,UpdateDate,t ype,UserName.
>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...Therefore 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.ForeignKeyField,@.RowMark2=d.PrimaryKey Field,@.RowMark3=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,RowMark2,RowMark3)
select 'Descriptive Table Name', convert(nvarchar(100), 'Descriptive
Field Name'),
convert(nvarchar(1000),d.FieldName),
convert(nvarchar(1000),i.FieldName),
@.UpdateDate, @.UserName, @.type,
COALESCE (d.ForeignKeyField, i.ForeignKeyField),
COALESCE (d.PrimaryKeyField, i.PrimaryKeyField),
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
DatetimeOfChange (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)|||Zlatko Mati (zlatko.matic1@.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.ForeignKeyField,@.RowMark2=d.PrimaryKey Field,@.RowMark3=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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks.

"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> je napisao u poruci interesnoj
grupi:9hpo41hcd4b2gifouvm3o13pj48r3ereqb@.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,FieldName,OldValue,NewValue,UpdateDate,t ype,UserName.
>>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...Therefore 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.ForeignKeyField,@.RowMark2=d.PrimaryKey Field,@.RowMark3=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,RowMark2,RowMark3)
> select 'Descriptive Table Name', convert(nvarchar(100), 'Descriptive
> Field Name'),
> convert(nvarchar(1000),d.FieldName),
> convert(nvarchar(1000),i.FieldName),
> @.UpdateDate, @.UserName, @.type,
> COALESCE (d.ForeignKeyField, i.ForeignKeyField),
> COALESCE (d.PrimaryKeyField, i.PrimaryKeyField),
> 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
> DatetimeOfChange (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)

audit tables, delete triggers, and asp.net

i'm in a bit of a bind at work. if anyone could help, i'd greatly
appreciate it.

i have a web app connecting to a sql server using sql server
authentication. let's say, for example, my login/password is
dbUser/dbUser. the web app however, is using windows authentication.
so if I am logged into the network as 'DOMAIN\Eric', when I access my
web app, my web app knows that I am 'DOMAIN\Eric'. but to the sql
server db, I am user 'dbUser'.

now, i for each table i have, i need to implement an audit table to
record all updates, inserts, deletes that occur against it. i was
going to do so with triggers. this is all fine for selects, inserts,
and updates. for each table, i have an updatedby and an updatedate.

for example, let's say i have a table:

create table blah
(
id int,
col1 varchar(10),
updatedby varchar(30),
updatedate datetime
)

and corresponding audit table:

create audit_blah
(
id int,
blah_id int,
blah_col1 varchar(10),
blah_updatedby varchar(1),
blah_updatedate datetime
)

for update and insert triggers, i can know what to insert into the
updatedby column of audit_blah because it's in a corresponding row in
blah. my web app knows what user is accessing the application, and
can insert that name into blah. blah's trigger will then insert that
name into audit_blah.

however, in the case of a delete, i'm not passing in an 'updatedby',
because i'm deleting. in this situation, how can the trigger know
what user is deleting? the db only knows that sql user 'dbUser' is
deleting, but doesn't know that 'dbUser' is deleting on behalf of
'DOMAIN\Eric'. is there any way for my app to inform the trigger to
access my windows identity without having a corresponding row in the
table from which to pull that info?

obviously, i could have each of my app's users log into SQL server
through Windows authentication; then i could just use SYSTEM_USER.
but let's say, for performance's sake, it'd be better for me to use
one sql server login. (i believe one user works better for connection
pooling purposes.) is there a way to get around this?

(i'm hoping a built-in function exists that solves all my problems.)

suggestions? resources?

any help would be great appreciated.

happy turkeys.

EricHi

You may want to do soft deletes instead (possibly with a garbage collection
job!) or do the deletes through a stored procedure and log them differently.

John
"ecastillo" <encee5@.gmail.com> wrote in message
news:a2f0a8ac.0411252044.5dffaa2@.posting.google.co m...
> i'm in a bit of a bind at work. if anyone could help, i'd greatly
> appreciate it.
> i have a web app connecting to a sql server using sql server
> authentication. let's say, for example, my login/password is
> dbUser/dbUser. the web app however, is using windows authentication.
> so if I am logged into the network as 'DOMAIN\Eric', when I access my
> web app, my web app knows that I am 'DOMAIN\Eric'. but to the sql
> server db, I am user 'dbUser'.
> now, i for each table i have, i need to implement an audit table to
> record all updates, inserts, deletes that occur against it. i was
> going to do so with triggers. this is all fine for selects, inserts,
> and updates. for each table, i have an updatedby and an updatedate.
> for example, let's say i have a table:
> create table blah
> (
> id int,
> col1 varchar(10),
> updatedby varchar(30),
> updatedate datetime
> )
> and corresponding audit table:
> create audit_blah
> (
> id int,
> blah_id int,
> blah_col1 varchar(10),
> blah_updatedby varchar(1),
> blah_updatedate datetime
> )
> for update and insert triggers, i can know what to insert into the
> updatedby column of audit_blah because it's in a corresponding row in
> blah. my web app knows what user is accessing the application, and
> can insert that name into blah. blah's trigger will then insert that
> name into audit_blah.
> however, in the case of a delete, i'm not passing in an 'updatedby',
> because i'm deleting. in this situation, how can the trigger know
> what user is deleting? the db only knows that sql user 'dbUser' is
> deleting, but doesn't know that 'dbUser' is deleting on behalf of
> 'DOMAIN\Eric'. is there any way for my app to inform the trigger to
> access my windows identity without having a corresponding row in the
> table from which to pull that info?
> obviously, i could have each of my app's users log into SQL server
> through Windows authentication; then i could just use SYSTEM_USER.
> but let's say, for performance's sake, it'd be better for me to use
> one sql server login. (i believe one user works better for connection
> pooling purposes.) is there a way to get around this?
> (i'm hoping a built-in function exists that solves all my problems.)
> suggestions? resources?
> any help would be great appreciated.
> happy turkeys.
> Eric|||[posted and mailed, please reply in news]

ecastillo (encee5@.gmail.com) writes:
> however, in the case of a delete, i'm not passing in an 'updatedby',
> because i'm deleting. in this situation, how can the trigger know
> what user is deleting? the db only knows that sql user 'dbUser' is
> deleting, but doesn't know that 'dbUser' is deleting on behalf of
> 'DOMAIN\Eric'. is there any way for my app to inform the trigger to
> access my windows identity without having a corresponding row in the
> table from which to pull that info?

You could use SET CONTEXT_INFO. This command is somewhat tricky to use,
but it's workable. This commands sets the column context_info in
sysprocesses. The value is a binary value. Here is an example:

declare @.bin varbinary(30)
select @.bin = convert(varbinary(30), 'DOMAIN\Eric')
set context_info @.bin
go
select convert(varchar(30), context_info)
from master..sysprocesses where spid = @.@.spid

The web server would do the first part, the trigger the second part.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

audit tables with composite keys

I am trying to write triggers on each tables in my database to audit data changes. My AuditLog table consists of the following columns -

LoginName varchar(100) - user name

Action varchar(5) - this will store 'INSERT','UPDATE','DELETE'

TableName varchar(30) - name of the table updated

PrimaryKey int - primary key of the record updated

ColumnName varchar(30) - name of the column updated

OldValue varchar(1000) - old value converted to varchar

NewValue varchar(1000) - new value converted to varchar

RecUpdDate datetime - record update date.

This table design will work for tables with single column primary keys. However, it will not work for tables with composite primary keys. Any suggestions on how to make this work with composite primary keys? I prefer not to change the tables in my database to use single column primary key.

Thanks in advance.

I had same kind of audit table. But i had PrimaryKey column as varchar. and we concatinate primarykeys of the table and insert in this table.

Madhu

|||

I had same kind of audit table. But i had PrimaryKey column as varchar. and we concatinate primarykeys of the table with a delimitter and insert in this table.

Madhu

|||

If you concatinate the primary keys, you need to make sure it's done is specific column order. If you want to use the primary key to locate the record in the data table, then you need to parse the string first. Do you find any performance issues with this method?

audit tables with composite keys

I am trying to write triggers on each tables in my database to audit data changes. My AuditLog table consists of the following columns -

LoginName varchar(100) - user name

Action varchar(5) - this will store 'INSERT','UPDATE','DELETE'

TableName varchar(30) - name of the table updated

PrimaryKey int - primary key of the record updated

ColumnName varchar(30) - name of the column updated

OldValue varchar(1000) - old value converted to varchar

NewValue varchar(1000) - new value converted to varchar

RecUpdDate datetime - record update date.

This table design will work for tables with single column primary keys. However, it will not work for tables with composite primary keys. Any suggestions on how to make this work with composite primary keys? I prefer not to change the tables in my database to use single column primary key.

Thanks in advance.

I had same kind of audit table. But i had PrimaryKey column as varchar. and we concatinate primarykeys of the table and insert in this table.

Madhu

|||

I had same kind of audit table. But i had PrimaryKey column as varchar. and we concatinate primarykeys of the table with a delimitter and insert in this table.

Madhu

|||

If you concatinate the primary keys, you need to make sure it's done is specific column order. If you want to use the primary key to locate the record in the data table, then you need to parse the string first. Do you find any performance issues with this method?

Audit Tables and triggers

Dear Group,

I would like to create an audit table that is created with a trigger that
reflects all the changes(insert, update and delete) that occur in table.

Say I have a table with

Subject_ID, visit_number, dob, weight, height, User_name, inputdate

The audit table would have .

Subject_ID, visit_number, dob, weight, height, User_name, inputdate,
edit_action, edit_reason.

Where the edit_action would be insert, update, delete; the edit_reason would
be the reason given for the edit.

Help with this would be great, since I am new to the world of triggers.

Thanks,

JeffJeff Magouirk (magouirkj@.njc.org) writes:
> I would like to create an audit table that is created with a trigger that
> reflects all the changes(insert, update and delete) that occur in table.
> Say I have a table with
> Subject_ID, visit_number, dob, weight, height, User_name,
> inputdate
> The audit table would have .
> Subject_ID, visit_number, dob, weight, height, User_name, inputdate,
> edit_action, edit_reason.
> Where the edit_action would be insert, update, delete; the edit_reason
> would be the reason given for the edit.
> Help with this would be great, since I am new to the world of triggers.

If you need to do to this on a broad scale, consider 3rd-party solutions.
Two that I usually recommend - although I've used none of them myself -
is SQLAudit from Red Matrix and Entegra from Lumigent. SQL Audit is
based on triggers, Entegra works from the transaction log.

But for a one-shot you could do:

CREATE TRIGGER tbl_audit_tri ON tbl FOR INSERT, UPDATE, DELETE

IF @.@.rowcount = 0
RETURN

IF EXISTS(SELECT * FROM inserted)
BEGIN
INSERT logtable (subject_id, ... edit_action)
SELECT subject_id, ...
CASE WHEN EXISTS (SELECT * FROM deleted)
THEN 'UPDATE'
ELSE 'INSERT'
FROM inserted
END
ELSE
BEGIN
INSERT logtable (subject_id, ... edit_action)
SELECT subject_id, ..., 'DELETE'
FROM delete
END

As you see I have left out edit_reason. This is because I don't know
what you mean with "edit_reason", and anyway it sounds like something
that can be quite difficult to get hold of from the trigger.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Audit table

Hi All
I'm looking for some code which create audit table and build triggers which
register all changes on table.
Maybe someone from you know where can I find it?
Regards
MichalChapter 6: Audit Logging in the book "Transact-SQL Cookbook" discuses audit
logging using triggers in detail:
http://vyaskn.tripod.com/transact-sql_cookbook.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Michal" <michalb77@.gazeta.pl> wrote in message
news:dfk13j$5hk$1@.atlantis.news.tpi.pl...
Hi All
I'm looking for some code which create audit table and build triggers which
register all changes on table.
Maybe someone from you know where can I find it?
Regards
Michal|||Hi,
Copy the Order table structure to AuditOrder table.
Select * into AuditOrders from Orders where 1= 2
Auditing trigger:-
--
CREATE TRIGGER Audittriiger_order ON Order
FOR INSERT, UPDATE, DELETE
AS
if @.@.rowcount = 0 return
DECLARE @.action char (7), @.inserted bit, @.deleted bit
set @.deleted = case when exists (select * from deleted) then 1 else 0 end
set @.inserted = case when exists (select * from inserted) then 1 else 0 end
set @.action = case when @.deleted = 1 and @.inserted = 1
then 'Before'
else 'DELETE'
end
if @.deleted = 1
begin
INSERT INTO AuditOrders ( operation, orderid, Order_name,
Order_qty,Order_date)
SELECT @.action AS F1,
RTRIM(SYSTEM_USER) AS F2,
[Order].Orderid,
[Order].Order_ name
FROM Deleted INNER JOIN Order ON Deleted.Orderid = Order.OrderID end
set @.action = case when @.deleted = 1 and @.inserted = 1
then 'After'
else 'INSERT'
end
if @.inserted = 1
begin
INSERT INTO AuditOrders( operation, orderid, Order_name,
Order_qty,Order_date)
SELECT @.action AS F1,
RTRIM(SYSTEM_USER) AS F2,
[Order].Orderid,
[Order].Order_ name
FROM Inserted INNER JOIN Order ON Inserted.Orderid = Order.OrderID
end
"Michal" <michalb77@.gazeta.pl> wrote in message
news:dfk13j$5hk$1@.atlantis.news.tpi.pl...
> Hi All
> I'm looking for some code which create audit table and build triggers
> which register all changes on table.
> Maybe someone from you know where can I find it?
> Regards
> Michal
>|||Michael may also want to have an additional date/time column in AuditOrder
that stores when the change took place (getdate) and perhaps another column
to store the system username (system_user) of who initiated the change.
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:e5ZpZ5tsFHA.1168@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Copy the Order table structure to AuditOrder table.
> Select * into AuditOrders from Orders where 1= 2
> Auditing trigger:-
> --
> CREATE TRIGGER Audittriiger_order ON Order
> FOR INSERT, UPDATE, DELETE
> AS
> if @.@.rowcount = 0 return
> DECLARE @.action char (7), @.inserted bit, @.deleted bit
> set @.deleted = case when exists (select * from deleted) then 1 else 0 end
> set @.inserted = case when exists (select * from inserted) then 1 else 0
> end
> set @.action = case when @.deleted = 1 and @.inserted = 1
> then 'Before'
> else 'DELETE'
> end
> if @.deleted = 1
> begin
> INSERT INTO AuditOrders ( operation, orderid, Order_name,
> Order_qty,Order_date)
> SELECT @.action AS F1,
> RTRIM(SYSTEM_USER) AS F2,
> [Order].Orderid,
> [Order].Order_ name
> FROM Deleted INNER JOIN Order ON Deleted.Orderid = Order.OrderID end
>
> set @.action = case when @.deleted = 1 and @.inserted = 1
> then 'After'
> else 'INSERT'
> end
> if @.inserted = 1
> begin
> INSERT INTO AuditOrders( operation, orderid, Order_name,
> Order_qty,Order_date)
> SELECT @.action AS F1,
> RTRIM(SYSTEM_USER) AS F2,
> [Order].Orderid,
> [Order].Order_ name
> FROM Inserted INNER JOIN Order ON Inserted.Orderid = Order.OrderID
> end
>
> "Michal" <michalb77@.gazeta.pl> wrote in message
> news:dfk13j$5hk$1@.atlantis.news.tpi.pl...
>