I need audit triggers that change columns value in the same record
that fire trigger.


I need how to do..

[quoted text, click to view]

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

[quoted text, click to view]

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
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

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





[quoted text, click to view]

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

[quoted text, click to view]