Very Computer

by **Travis Labor** » Thu, 24 Aug 2000 04:00:00

Hello, I am using version 7, not sure which service pack...

I have a table such as a simple "client" table with the "normal"
fields such as client_id, client_name, client_whatever.

In addition, I have an "audit" table, which is used to record changes
to the "client" table.

This is done through an UPDATE trigger on the "client" table.

To make explanation of the problem very simple, lets assume for now
that the "audit" table only shows "client x was updated today."

The problem is, when I do an sql UPDATE statement that affects
multiple clients, all clients are correctly modified, BUT only ONE of
them actually fires the trigger, thus creating ONE entry in the
"audit" table.

EXAMPLE:

update clients set client_rate = 5 where client_type = 2

That statement would change the rate on 3 of my clients in the clients
table, but only one entry would be made in the audit table.

I need 3 entries in the audit table for the above situation. Please
help me if you can!

Travis Laborde

by **Andrew J. Kell** » Thu, 24 Aug 2000 04:00:00

Travis,

Since you didn't post the trigger code I will make an assumption here.
You probably have a single insert statement in the trigger to the Audit
table. What you need to do is do an Insert from a join on the Inserted or
Deleted table to get each row that was modified.

Andy

> Hello, I am using version 7, not sure which service pack...

> I have a table such as a simple "client" table with the "normal"
> fields such as client_id, client_name, client_whatever.

> In addition, I have an "audit" table, which is used to record changes
> to the "client" table.

> This is done through an UPDATE trigger on the "client" table.

> To make explanation of the problem very simple, lets assume for now
> that the "audit" table only shows "client x was updated today."

> The problem is, when I do an sql UPDATE statement that affects
> multiple clients, all clients are correctly modified, BUT only ONE of
> them actually fires the trigger, thus creating ONE entry in the
> "audit" table.

> EXAMPLE:

> update clients set client_rate = 5 where client_type = 2

> That statement would change the rate on 3 of my clients in the clients
> table, but only one entry would be made in the audit table.

> I need 3 entries in the audit table for the above situation. Please
> help me if you can!

> Travis Laborde

by **A Khali** » Thu, 24 Aug 2000 04:00:00

from within your trigger you can refer to the imaginary table called
"Inserted",which has
the exact same structure as your original table and it contains all rows
that are being
updated.

You have two options.
1. Declare a cursor on "Inserted" table. step through the records, and for
each record insert
a record in your "audit" table.

2. do something like
Insert Into Audit (Text)
Select Client_ID + " Record was updated"
From Inserted

>Hello, I am using version 7, not sure which service pack...

>I have a table such as a simple "client" table with the "normal"
>fields such as client_id, client_name, client_whatever.

>In addition, I have an "audit" table, which is used to record changes
>to the "client" table.

>This is done through an UPDATE trigger on the "client" table.

>To make explanation of the problem very simple, lets assume for now
>that the "audit" table only shows "client x was updated today."

>The problem is, when I do an sql UPDATE statement that affects
>multiple clients, all clients are correctly modified, BUT only ONE of
>them actually fires the trigger, thus creating ONE entry in the
>"audit" table.

>EXAMPLE:

>update clients set client_rate = 5 where client_type = 2

>That statement would change the rate on 3 of my clients in the clients
>table, but only one entry would be made in the audit table.

>I need 3 entries in the audit table for the above situation. Please
>help me if you can!

>Travis Laborde

Very Computer

UPDATE trigger - multiple rows affected by update

UPDATE trigger - multiple rows affected by update

UPDATE trigger - multiple rows affected by update

UPDATE trigger - multiple rows affected by update