Need advice on using a simple trigger and update

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));

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! Received on Thu Jan 30 2003 - 11:08:15 CST