UPDATE trigger - multiple rows affected by update

UPDATE trigger - multiple rows affected by update

Post 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

 
 
 

UPDATE trigger - multiple rows affected by update

Post 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



 
 
 

UPDATE trigger - multiple rows affected by update

Post 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


 
 
 

1. My Trigger updates the complete tabe not only the affected row

Hi NG,

I will create a trigger ( it's my first ) to update a the field
'LOGDATE'
( for last modification date ).
My Trigger updates the complete table, not only the affected row which
was inserted ore updated.

SQL:
CREATE TRIGGER UPDATE_LOGDATE ON [dbo].amr_konzerne
FOR INSERT, UPDATE
AS
update amr_konzerne
set logdate=CURRENT_TIMESTAMP

Thank for your help in advanced.

Regards
Alf

2. Getting rid of the FOX ICON (VFP) from the main app desktop title bar.

3. When updating recordset I get error -2147467259 (to many rows affected by update)

4. Form question

5. How to update multiple rows using UPDATE

6. Multiple Join query running to long.

7. Update trigger - Set statement returns multiple rows

8. Looking for a Stress Tool

9. Update field on updated row with trigger??

10. Triggers: multiple row update

11. Referential trigger with multiple row updates

12. Triggers and multiple row/column updates

13. Using a trigger to update a time field in a table when a row is updated