Another DB Trigger Question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Another DB Trigger Question

  1. #1
    Join Date
    Oct 2000
    Posts
    123
    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]

  2. #2
    Join Date
    Mar 2001
    Location
    Cologne, Germany
    Posts
    24

    Wink

    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


  3. #3
    Join Date
    Oct 2000
    Posts
    123
    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

  4. #4
    Join Date
    Mar 2001
    Posts
    13
    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 ??

  5. #5
    Join Date
    Oct 2000
    Posts
    123
    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
  •  



Click Here to Expand Forum to Full Width