triggers audit

[raulgz]

I need audit triggers that change columns value in the same record
that fire trigger.
I need how to do..
Thanks..

[Chuck Conover]

Raulgz,
Unfortunately, MS SQL Server 2000 does not offer BEFORE triggers, which
would allow you to massage the data in a trigger before the insert/update.
I am hoping for this functionality in the next major release.
The best way to do this kind of data massaging is to upload data to a
temp table first, then massage the data, then insert it into your production
table, or to have the application do the data massaging. But, if that is
not possible, here is the way to do it:

CREATE TRIGGER
TIB_TR_SHIPMENT_DETAIL_GROUP
ON TR_SHIPMENT_DETAIL
FOR INSERT
NOT FOR REPLICATION
AS
BEGIN

if SYSTEM_USER = 'SQL_Repl' OR SYSTEM_USER = 'SQLSrvLogin'
RETURN

UPDATE TR_SHIPMENT_DETAIL
SET
subgroup_cd = dbo.f_get_subgroup(tr_shipment_detail.comp_cd)
FROM TR_SHIPMENT_DETAIL tr
WHERE EXISTS
(SELECT i.*
FROM inserted i
WHERE i.SHIP_KEY = tr.SHIP_KEY)
END

Explanation: First, don't allow any trigger to fire if the data is
replicated from another database. This creates a bounce effect whereby
changes may bounce from db to db and never stop. So this is the reason for
the NOT FOR REPLICATION and the If SYSTEM_USER... statements. Both are not
needed, since either should do the job.
Second, triggers are not fired on a row by row basis (oh, how I wish
that was an option), but instead they are triggered once for each
transaction on the table. So, if one transaction inserts/updates multiple
records, there will be multiple records in the "inserted" table. This is
the reason for the WHERE EXISTS clause.
The dbo.f_get_subgroup() function is a user-defined function to get the
subgroup and is specific to this database, but it does show how you might
set a value in a trigger based on a function.

Hope this helps.
There is a much more detailed explanation in our section on Triggers at
www.TechnicalVideos.net.
Best regards,
Chuck Conover
www.TechnicalVideos.net

"raulgz" <ra****@ozu.es> wrote in message
news:9b*************************@posting.google.co m...

I need audit triggers that change columns value in the same record
that fire trigger.
I need how to do..
Thanks..

[Simon Hayes]

ra****@ozu.es (raulgz) wrote in message news:<9b*************************@posting.google.c om>...

I need audit triggers that change columns value in the same record
that fire trigger.
I need how to do..
Thanks..

I'm not sure I understand exactly what you need, but perhaps something like this?

create trigger MyTrigger
on MyTable
after update
as
begin
if @@rowcount = 0
return

update MyTable
set UpdatedTime = getdate(), UpdatedBy = suser_sname()
from MyTable t
join inserted i
on t.PrimaryKeyColumn = i.PrimaryKeyColumn
end
go

Simon

[Daniel Morgan]

Chuck Conover wrote:

Raulgz,
Unfortunately, MS SQL Server 2000 does not offer BEFORE triggers, which
would allow you to massage the data in a trigger before the insert/update.

Given that BEFORE triggers have existed in other RDBMS product lines for
almost 20 years it certainly would be nice of them to get around to it:
Maybe during the current decade. Then perhaps they can start
contemplating the other basic functionality that is still missing from
the product.

--
Daniel Morgan
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[raul garcia zamarra]

Thanks,

I know this solution using inserted table,
but when l make one insert this trigger
produce one insert and nine updates

I can avoid this behaviour.
Thank very much again.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[raul garcia zamarra]

Thanks,

I know this solution using inserted table,
but when l make one insert this trigger
produce one insert and nine updates

I can avoid this behaviour.
Thank very much again.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Harold Helmich]

If you want you can turn off recursive triggers. Review SQL help file.
sp_configure.

raul garcia zamarra wrote:

Thanks,

I know this solution using inserted table,
but when l make one insert this trigger
produce one insert and nine updates

I can avoid this behaviour.
Thank very much again.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

raul garcia zamarra (ra****@ozu.es) writes:

I know this solution using inserted table,
but when l make one insert this trigger
produce one insert and nine updates

I can avoid this behaviour.

The trigger that Simon posted will only cause one update, unless the
database option RECURSIVE_TRIGGERS is active, in which case the trigger
will exceed the maximum nesting level and crash.

Maybe you could clarify what you mean with nine updates.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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

[Greg D. Moore \(Strider\)]

"Daniel Morgan" <da******@x.washington.edu> wrote in message
news:1075301899.586131@yasure...

Chuck Conover wrote:

Raulgz,
Unfortunately, MS SQL Server 2000 does not offer BEFORE triggers, which would allow you to massage the data in a trigger before the

insert/update.
Given that BEFORE triggers have existed in other RDBMS product lines for
almost 20 years it certainly would be nice of them to get around to it:
Maybe during the current decade. Then perhaps they can start
contemplating the other basic functionality that is still missing from
the product.

They do have INSTEAD OF which I understand is not exactly the same, but
offers similar functionality.

--
Daniel Morgan
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[raul garcia zamarra]

Thanks,

I don`t change recursive_triggers to false
because this change affect all triggers in
my database sql server.

Do you know another solution ???
Thank you very much.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[raul garcia zamarra]

Thanks,

I activate execution plan and
this window write nine updates
on rows when I update the same
table in trigger after update.

I don`t change recursive_triggers to false
because this change affect all triggers in
my database sql server.

Do you know another solution ???
Thank you very much.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

raul garcia zamarra (ra****@ozu.es) writes:

I activate execution plan and
this window write nine updates
on rows when I update the same
table in trigger after update.
I'm not really sure what you mean here, but it does not sound like anything
to worry about. Since you have not given a full repro for your case, it
is a little difficult to run an example myself, to see what you are
talking about. But at least, that is not a token of a recursive trigger.
I don`t change recursive_triggers to false
because this change affect all triggers in
my database sql server.

Just to clear things out: there are *two* triggers settings in SQL
Server.

One is *nested triggers*. This is a server-wide configuration option
that is *on* by default. This option says that if you in an trigger
perform an INSERT/DELETE/TABLE on *another* table, any triggers on
that table will fire. This setting does not control what happens
if you update the table to which the trigger belongs.

The other setting is RECURSIVE_TRIGGERS, and this is a database-wide
setting, which is *off* by default. When this setting is active,
if you update the same table in a trigger, the trigger will fire
anew.

Do you really have RECURSIVE_TRIGGERS on? And do you really need to?
Recursive triggers are quite and advanced concept, as you must have
a way to stop recursion. There is a simple way though, if you want a
particular trigger to be non-recursive:

if trigger_nestlevel(@@procid) > 1
return

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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