Another DB Trigger Question

[mber]

Hi,

I have two tables: table1 and table2, and i want to create a trigger like:
create or replace trigger trig_1
after insert on table1
....
update table2 set col1 = ....(based on sum(col2 of table1))
....
Within this trigger i want to update table2. This update is based on column1 in table1(basically on summation of column1 for table1), if i do "select sum(col1) from table1" ---- they must be mutating, please help.

BTW, there is not foreign key relationship and the col1 in table2, col2 in table1 are not keys in both tables.

Thanks

[Edited by mber on 03-27-2001 at 11:30 PM]

[6502]

Hi !

Use a trigger which ist not firing 'for each row', but for each statement:
--------------cut here-------------------
CREATE OR REPLACE TRIGGER test
AFTER INSERT
ON table1
declare
v_sum number;

Begin
select sum(something)
into v_sum
from table1;

UPDATE table2
SET sum1 = v_sum;
End;
/
--------------cut here-------------------
I tested it, it's working fine.

Commit;
6502

[mber]

Thank you for you help.

but i want to update the column based on specific key columns, and one key column have multiple potential rows, so i can not only use statment trigger. I have solve that anyway.

Thanks again

[meghna]

Originally posted by mber
Thank you for you help.

but i want to update the column based on specific key columns, and one key column have multiple potential rows, so i can not only use statment trigger. I have solve that anyway.

Thanks again

Hi,

Can you help me in this ...
How did u solve your problem ??

[mber]

Basically, i create variables in my package, then create a trigger "before insert on table1 for each row", store the key in one of the variables created in my package;create second trigger "before insert on table1" statement level, select(sum) the column i wanted based on the variable i stored formly, store the summation to another variable in the package; last, create trigger "after insert on table1 for each row" and do whatever you want based on the infos stroed in the variables in the package.

Hope i'm clear,

Take care