Creating an Audit trail on a table using a trigger

Hi,
This is kind of following on from my last couple of posts regarding Identity
columns and so on.
Basically, I want to ensure that for a particular table, every row has a
numeric reference. This reference must be unique and gapless. Ideally it
should order in the sequence of the records being inserted however this
isn't an absolute requirement.
From my (limited) understanding of SQLS, I think I can achieve this with a
FOR INSERT Trigger - in that the trigger is fired every time a row is
inserted and the trigger is the same transaction as the initial insert hence
I avoid any concurrency issues.
However I'm not completely sure how to achieve this. I think that my trigger
should be along the lines of this...
CREATE TRIGGER AssignAuditReference ON tblBooking
FOR INSERT
AS
DECLARE @.Ref int
--Get the highest reference and add one.
SELECT @.Ref = isnull(max(job_id),0)+1 from tblBooking
--Update the inserted row to have a booking_referecen of the new reference
obtained above.
Update tblBooking
SET Booking_Reference = @.ref
WHERE Booking_ID = INSERTED.Booking_ID
However I'm getting problems with the INSERTED table not being recognised.
I understood that the INSERTED table contained the row that the insert that
started the trigger inserted.
Two questions:
1. Where am I going wrong with my trigger. Have I misunderstood some key
point of using triggers.
2. Is this the right approach to achieve what I am after? Are there any
better approaches...Hi Chris,
You have to mention the Inserted Table in your Update Query
Update tblBooking
SET tblBooking.Booking_Reference = @.Ref
FROM tblBooking
INNER JOIN INSERTED
ON (tblBooking.Booking_ID= INSERTED.Booking_ID)
Because of the lack between getting the @.Ref-Value and writing it in the
table i would prefer an inline Query and Update
Update tblBooking
SET tblBooking.Booking_Reference = NewJobIdTable.NewJobId
FROM tblBooking,
(
Select ISNULL(MAX(job_id),0)+1 AS NewJobId from tblBooking
) NewJobIdTable
INNER JOIN INSERTED
ON (tblBooking.Booking_ID= INSERTED.Booking_ID)
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Chris Strug" <hotmail@.solace1884.com> schrieb im Newsbeitrag
news:eBcd9GOQFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This is kind of following on from my last couple of posts regarding
> Identity
> columns and so on.
> Basically, I want to ensure that for a particular table, every row has a
> numeric reference. This reference must be unique and gapless. Ideally it
> should order in the sequence of the records being inserted however this
> isn't an absolute requirement.
> From my (limited) understanding of SQLS, I think I can achieve this with a
> FOR INSERT Trigger - in that the trigger is fired every time a row is
> inserted and the trigger is the same transaction as the initial insert
> hence
> I avoid any concurrency issues.
> However I'm not completely sure how to achieve this. I think that my
> trigger
> should be along the lines of this...
> CREATE TRIGGER AssignAuditReference ON tblBooking
> FOR INSERT
> AS
> DECLARE @.Ref int
> --Get the highest reference and add one.
> SELECT @.Ref = isnull(max(job_id),0)+1 from tblBooking
> --Update the inserted row to have a booking_referecen of the new
> reference
> obtained above.
> Update tblBooking
> SET Booking_Reference = @.ref
> WHERE Booking_ID = INSERTED.Booking_ID
> However I'm getting problems with the INSERTED table not being recognised.
> I understood that the INSERTED table contained the row that the insert
> that
> started the trigger inserted.
> Two questions:
> 1. Where am I going wrong with my trigger. Have I misunderstood some key
> point of using triggers.
> 2. Is this the right approach to achieve what I am after? Are there any
> better approaches...
>|||Syntactically, your UPDATE statement is missing the FROM clasue:
UPDATE tblBooking
SET Booking_Reference = @.ref
FROM tblBooking , INSERTED
WHERE Booking_ID = INSERTED.Booking_ID
1) This will FAIL if more than one row is inserted - not a good idea
for maintaining an audit trail. 2) I don't see what advantage this has
over the more concise and reliable solution(s) already discussed in
your earlier threads. For example:
INSERT INTO tblBooking (booking_reference, x, y, z, ...)
SELECT COALESCE(MAX(booking_reference),0)+1, 'foo', 'bar', 1234, ...
FROM tblBooking
IMO an incrementing counter is a poor way to maintain an audit trail
anyway. Why not just store the CURRENT_TIMESTAMP on each row and then
preserve the history of changes to rows? This is easy to do in triggers
or in your data access code and doesn't suffer the inevitable and
serious blocking problems that your approach implies.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1113478283.991829.222620@.g14g2000cwa.googlegroups.com...
> Syntactically, your UPDATE statement is missing the FROM clasue:
> UPDATE tblBooking
> SET Booking_Reference = @.ref
> FROM tblBooking , INSERTED
> WHERE Booking_ID = INSERTED.Booking_ID
> 1) This will FAIL if more than one row is inserted - not a good idea
> for maintaining an audit trail. 2) I don't see what advantage this has
> over the more concise and reliable solution(s) already discussed in
> your earlier threads. For example:
> INSERT INTO tblBooking (booking_reference, x, y, z, ...)
> SELECT COALESCE(MAX(booking_reference),0)+1, 'foo', 'bar', 1234, ...
> FROM tblBooking
> IMO an incrementing counter is a poor way to maintain an audit trail
> anyway. Why not just store the CURRENT_TIMESTAMP on each row and then
> preserve the history of changes to rows? This is easy to do in triggers
> or in your data access code and doesn't suffer the inevitable and
> serious blocking problems that your approach implies.
> --
> David Portas
> SQL Server MVP
> --
>
First of all thanks to both David and Jens for their replies.
Apologies for repeating myself, I just want to make sure that I understand
what I'm doing rather than repeating it parrot fashion into my database.
Regarding the trigger, I was under the impression that the trigger would
occur for every new row, I gather that it in fact applies to every INSERT.
Ahh... That makes things clearer.
Regards the actual implementation (TIMESTAMP vs. numeric reference),
unfortunately this is out of my hands. I've been informed that this a is a
non negotiatable requirement. What can you do?
if I may ask one more question, assuming that I did attempt to implement my
apprioach using triggers, could you expand on the blocking problems that you
would expect me to face?
Anyway, thank you once again for taking the time to help me, I do appreciate
it.
Regards
Chris.

Creating a View

I have 2 tables. T1 is for current data. T2 is a audit tracking table for T1. There will be several records in T2 for each 1 in T1. T2 has a Action Field that stores the last action and a auditID to record changes on T1.

What I want to do is create a view that shows the current records in T1 and all the records in the audit tabel T2. I can do the Join but this would duplicate all the fields.

I am looking for something like this:

Select 'AuditID' AuditID,TD.*,'Action' Action from TrakrDetails TD
--Union
Select TDA.* from TrakrDetails_Audit TDA
order by AuditID desc

This craps out because there are 2 additional Fields in T2.

Any Suggestions?

Thanks
JonSorry but you have to list out all of the columns...

If you want to show columns that aren't in the other table you can use a literal like space, or you can use a null

SELECT ' ' AS Col1,
, Null As Col2
, Col3 FROM myTable99
UNION ALL
SELECT Col1
, Col2
, Col3
FROM myTable00|||Thanks Brett

Your way works.

Normally I would have done it this way but it seemed like it was the long way around (thats the way it normally goes for me).

I thought there might be a easy way I was missing.

Thanks Again
Jon|||As an aside NEVER use SELECT *

(Except for analysis, never for code...save yourself a lot of pain)

Creating a trigger using a cursor

Hi all,
I need to create a trigger on all tables in a database that will insert into
an audit table username, and event on the table. I can create the trigger
individually, but I would like to put this into a cursor so I do not have to
run the trigger 500 times.
I am grabbing all user tables and trying to exec a string within the cursor
to create the triggers. I keep gettin eror by kyword insert. which I believe
is near
" INSERT INTO #inputbuffer"
Below is the code I am using:
TIA,
Joe
declare @.name varchar(100), @.str varchar(8000)
declare crscall cursor for
select name from sysobjects
where type = 'u'
open crscall
fetch next from crscall
into @.name
while @.@.Fetch_Status = 0
begin
declare @.str varchar(8000),@.name varchar(50)
set @.name = 'testrights'
select @.str = 'IF EXISTS (SELECT name FROM sysobjects
WHERE name = '+''''+@.name+'_Audit_InsUpd'+''''+' AND type =
'+''''+'TR'+''''+')
DROP TRIGGER Audit_InsUpd'
exec (@.str)
select @.str = 'CREATE TRIGGER '+@.name+'_Audit_InsUpd
ON '+@.name+
'FOR INSERT, UPDATE AS
BEGIN
SET NOCOUNT ON
DECLARE @.ExecStr varchar(50), @.Qry nvarchar(255)
CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
SET @.ExecStr = '+''''+'DBCC INPUTBUFFER('+ STR(@.@.SPID)+')'+''''+char(13)+
' INSERT INTO #inputbuffer
EXEC (@.ExecStr)
SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
insert into Tbl_MSDBAudit
select SUSER_SNAME(),@.qry
END'
select @.str
exec (@.str)
fetch next from crscall
into @.name
end
close crsCAll
deallocate crsCAllAre you sure you really want this kind of automation?
Anyway, change the script to print out the query strings instead of just
executing them. Then test them: parse them and attempt to execute them.
And when it's done - I don't want to scare you - you'll still have to test
them 500 times.
ML|||Thank you daniel,
I guess it was just an extra pair of eyes!
The first typo did the trick.
Thanks again.
Joe|||Thank you! this was helpful as well as Daniels.|||This is a one-time thing to create all the triggers so it won't get into
production code.
So good for the poster if he can automate the creation of the triggers.
But I agree with out on the last part, he's still have to test them all.
Maybe he can automate that part too. ;-)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:8C32588A-D032-40D3-B554-D6A64EB83F15@.microsoft.com...
> Are you sure you really want this kind of automation?
> Anyway, change the script to print out the query strings instead of just
> executing them. Then test them: parse them and attempt to execute them.
> And when it's done - I don't want to scare you - you'll still have to test
> them 500 times.
>
> ML|||If he puts his mind to it, someday his entire life might get automated. :)
He'll have automated himself out of existence.
ML|||That's what I am looking for. Automation is a wonderful thing!|||Well, I wish you good luck on your journey. :)
I hope those 500 tables weren't created automatically by mistake... ;)
ML|||jaylou wrote on Thu, 28 Jul 2005 07:01:13 -0700:

> Hi all,
> I need to create a trigger on all tables in a database that will insert
> into an audit table username, and event on the table. I can create the
> trigger individually, but I would like to put this into a cursor so I do
> not have to run the trigger 500 times.
> I am grabbing all user tables and trying to exec a string within the
> cursor to create the triggers. I keep gettin eror by kyword insert. which
> I believe is near
> " INSERT INTO #inputbuffer"
> Below is the code I am using:
Did you copy and paste that code? If so, there are 2 errors I spotted
straight away, both near the word INSERT. Comments inline, look for Typo #1
and Typo #2.
Dan

> TIA,
> Joe
> declare @.name varchar(100), @.str varchar(8000)
> declare crscall cursor for
> select name from sysobjects
> where type = 'u'
> open crscall
> fetch next from crscall
> into @.name
> while @.@.Fetch_Status = 0
> begin
> declare @.str varchar(8000),@.name varchar(50)
> set @.name = 'testrights'
> select @.str = 'IF EXISTS (SELECT name FROM sysobjects
> WHERE name = '+''''+@.name+'_Audit_InsUpd'+''''+' AND type =
> '+''''+'TR'+''''+')
> DROP TRIGGER Audit_InsUpd'
> exec (@.str)
> select @.str = 'CREATE TRIGGER '+@.name+'_Audit_InsUpd
> ON '+@.name+
> 'FOR INSERT, UPDATE AS
Typo #1. There's no space between ' and FOR, so you'd end up with invalid
syntax here as the table name will be concatenated into FOR and then the
INSERT keyword is invalid as there is no FOR.

> BEGIN
> SET NOCOUNT ON
> DECLARE @.ExecStr varchar(50), @.Qry nvarchar(255)
> CREATE TABLE #inputbuffer
> (
> EventType nvarchar(30),
> Parameters int,
> EventInfo nvarchar(255)
> )
> SET @.ExecStr = '+''''+'DBCC INPUTBUFFER('+ STR(@.@.SPID)+')'+''''+char(13)+
> ' INSERT INTO #inputbuffer
Typo #2. There's a ' missing at the start of this line, so this INSERT won't
be inside the string being assigned to @.str, it's going to be run in the
trigger creating code and #inputbuffer doesn't yet exist as a table.
However, I'm pretty sure the error is due to typo #1 otherwise you'd have
received an error about table #inputbuffer not existing, the compiler might
not be getting this far.

> EXEC (@.ExecStr)
> SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
> insert into Tbl_MSDBAudit
> select SUSER_SNAME(),@.qry
> END'
> select @.str
> exec (@.str)
> fetch next from crscall
> into @.name
> end
> close crsCAll
> deallocate crsCAll
>|||Hi
Run this Code
declare @.str varchar(8000),@.name varchar(50)
declare crscall cursor for
select name from sysobjects
where type =3D 'u'
open crscall
fetch next from crscall
into @.name
while @.@.Fetch_Status =3D 0
begin
--set @.name =3D 'testrights'
select @.str =3D 'IF EXISTS (SELECT name FROM sysobjects
WHERE name =3D '+''''+@.name+'_Audit_InsUpd'+'=AD'''+' AND type =3D
'+''''+'TR'+''''+')
DROP TRIGGER Audit_InsUpd'
exec (@.str)
select @.str =3D 'CREATE TRIGGER '+@.name+'_Audit_InsUpd
ON '+@.name+
' FOR INSERT, UPDATE AS
BEGIN
SET NOCOUNT ON
DECLARE @.ExecStr varchar(50), @.Qry nvarchar(255)
CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
SET @.ExecStr =3D '+''''+'DBCC INPUTBUFFER('+
STR(@.@.SPID)+')'+''''+char(13)+
' INSERT INTO #inputbuffer
EXEC (@.ExecStr)
SET @.Qry =3D (SELECT EventInfo FROM #inputbuffer)
insert into Tbl_MSDBAudit
select SUSER_SNAME(),@.qry
END'
select @.str
exec (@.str)
fetch next from crscall
into @.name
end
close crsCAll=20
deallocate crsCAll=20
With warm regards
Jatinder Singhsql

Creating a trigger on a table using a cursor.

Good Day All,
I am trying to create a trigger on a table and this trigger must update
an Audit table which reflects the column name (the changes apply to),
the old value and the new value.
I have tried running through a cursor to dynamically update the Audit
table with the individual fields but this does not work since when
selecting from the inserted or deleted table one can either select all
fields or certain fields but I find it difficult to select only values
for the field that is current on my cursor.
I really will appreciate your help.
Regards,
Phonzo.I would caution against using a cursor inside a trigger.
Normally, when creating Audit trails, it is only necessary to append the
contents of deleted and/or inserted to the Audit table. And the Audit table
'should' have at least a couple of additional columns: 'WhoDoneIt' default
SYSTEM_USER, 'WhenDoneIt' default getdate().
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Phonzo" <alphonse.zulu@.treehousemis.com> wrote in message
news:1157552615.607572.152860@.m73g2000cwd.googlegroups.com...
> Good Day All,
> I am trying to create a trigger on a table and this trigger must update
> an Audit table which reflects the column name (the changes apply to),
> the old value and the new value.
>
> I have tried running through a cursor to dynamically update the Audit
> table with the individual fields but this does not work since when
> selecting from the inserted or deleted table one can either select all
> fields or certain fields but I find it difficult to select only values
> for the field that is current on my cursor.
>
> I really will appreciate your help.
>
> Regards,
> Phonzo.
>|||Hi Arnie,
Thanks a lot for this info. Much appreciated.
Thanks,
Regards,
Phonzo.
Arnie Rowland wrote:
> I would caution against using a cursor inside a trigger.
> Normally, when creating Audit trails, it is only necessary to append the
> contents of deleted and/or inserted to the Audit table. And the Audit table
> 'should' have at least a couple of additional columns: 'WhoDoneIt' default
> SYSTEM_USER, 'WhenDoneIt' default getdate().
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Phonzo" <alphonse.zulu@.treehousemis.com> wrote in message
> news:1157552615.607572.152860@.m73g2000cwd.googlegroups.com...
> > Good Day All,
> >
> > I am trying to create a trigger on a table and this trigger must update
> >
> > an Audit table which reflects the column name (the changes apply to),
> > the old value and the new value.
> >
> >
> > I have tried running through a cursor to dynamically update the Audit
> > table with the individual fields but this does not work since when
> > selecting from the inserted or deleted table one can either select all
> > fields or certain fields but I find it difficult to select only values
> > for the field that is current on my cursor.
> >
> >
> > I really will appreciate your help.
> >
> >
> > Regards,
> > Phonzo.
> >

Creating a trigger on a table using a cursor.

Good Day All,
I am trying to create a trigger on a table and this trigger must update
an Audit table which reflects the column name (the changes apply to),
the old value and the new value.
I have tried running through a cursor to dynamically update the Audit
table with the individual fields but this does not work since when
selecting from the inserted or deleted table one can either select all
fields or certain fields but I find it difficult to select only values
for the field that is current on my cursor.
I really will appreciate your help.
Regards,
Phonzo.I would caution against using a cursor inside a trigger.
Normally, when creating Audit trails, it is only necessary to append the
contents of deleted and/or inserted to the Audit table. And the Audit table
'should' have at least a couple of additional columns: 'WhoDoneIt' default
SYSTEM_USER, 'WhenDoneIt' default getdate().
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Phonzo" <alphonse.zulu@.treehousemis.com> wrote in message
news:1157552615.607572.152860@.m73g2000cwd.googlegroups.com...
> Good Day All,
> I am trying to create a trigger on a table and this trigger must update
> an Audit table which reflects the column name (the changes apply to),
> the old value and the new value.
>
> I have tried running through a cursor to dynamically update the Audit
> table with the individual fields but this does not work since when
> selecting from the inserted or deleted table one can either select all
> fields or certain fields but I find it difficult to select only values
> for the field that is current on my cursor.
>
> I really will appreciate your help.
>
> Regards,
> Phonzo.
>|||Hi Arnie,
Thanks a lot for this info. Much appreciated.
Thanks,
Regards,
Phonzo.
Arnie Rowland wrote:[vbcol=seagreen]
> I would caution against using a cursor inside a trigger.
> Normally, when creating Audit trails, it is only necessary to append the
> contents of deleted and/or inserted to the Audit table. And the Audit tabl
e
> 'should' have at least a couple of additional columns: 'WhoDoneIt' default
> SYSTEM_USER, 'WhenDoneIt' default getdate().
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Phonzo" <alphonse.zulu@.treehousemis.com> wrote in message
> news:1157552615.607572.152860@.m73g2000cwd.googlegroups.com...