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_DET AIL
FOR INSERT
NOT FOR REPLICATION
AS
BEGIN

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

UPDATE TR_SHIPMENT_DET AIL
SET
subgroup_cd = dbo.f_get_subgr oup(tr_shipment _detail.comp_cd )
FROM TR_SHIPMENT_DET AIL 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_subgr oup() 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.e s> wrote in message
news:9b******** *************** **@posting.goog le.com...

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.goo gle.com>...

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.PrimaryKeyCol umn = i.PrimaryKeyCol umn
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.wash ington.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_TRIGG ERS 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.was hington.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.wash ington.edu
(replace 'x' with a 'u' to reply)

[raul garcia zamarra]

Thanks,

I don`t change recursive_trigg ers 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!