-
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]
-
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
-
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
-
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 ??
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|