Re: Need advice on using a simple trigger and update

oranewbie wrote:
> Hi,
> I have the following "master" table with two rows.
>
> file_id, description, Flag
> 0001 Financials Y
> 0002 Rates Y
>
> When I run my trigger I need to insert the two rows from above into
> just one row on the new "trigger" table:
>
> Like so....
>
> sysdate, fiancial_file_id, rate_file_id,
> 01/31/03 0001 0002
>
>
> Here is my current trigger script:
>
> CREATE OR REPLACE TRIGGER my_trigger
> BEFORE UPDATE OR DELETE OR INSERT ON master_tb
> for each row
> WHEN (OLD.FILE_ID IN ('RATES','FINANCIALS') AND OLD.FLAG = 'Y')
> BEGIN
> insert into trigger_tb
> (create_dt,
> financial_file_id,
> rate_file_id)
> VALUES
> (SYSDATE,
> decode(:OLD.description,'FINANCIALS',:OLD.file_id),
> decode(:OLD.description,'RATES',:OLD.file_id));
> END;
> /
>
>
> What I'm seeing when I run my script is that my trigger_tb looks like
> this instead:
>
> sysdate, fiancial_file_id, rate_file_id,
> 01/31/03 0001
> 01/31/03 0002
>
>
> Is it possible to get the id's(financial,rate) to be in just one row?
> Do I need to write another trigger to update the trigger_tb?
>
> thanks for any ideas!
You get that behavior because:

1. You insert the Financials record in master_tb and the trigger fires.
2. You insert the Rates record in master_tb and the trigger fires.

Two events, trigger fires twice, trigger_tb gets two records. You can't write a trigger against trigger_tb to update trigger_tb (you'll get the mutating tables error). Received on Thu Jan 30 2003 - 19:35:38 CET