SEO

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


I need how to do..


Thanks..

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" <ragaza@ozu.es> wrote in message
news:9b551742.0401280215.a186096@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..

ragaza@ozu.es (raulgz) wrote in message news:<9b551742.0401280215.a186096@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

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

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!

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!

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!

raul garcia zamarra (ragaza@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, sommar@algonet.se

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

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

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!