Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    81

    Unanswered: Variable in trigger

    I need a method to set a variable to a certain value during execute of a trigger. Normally I use a SEQUENCE and ...FOR EACH ROW. But that gives me a new number each time.

    It is difficult to describe, so let me try with some SQL:

    CREATE SEQUENCE TMP_COUNTER1;
    CREATE TABLE tmp_table1 (col1 number);
    CREATE TABLE tmp_table2 (col1 number, col2 number);

    CREATE TRIGGER tmp_trigger1 before delete on tmp_table1
    for each row
    declare
    counter1 number;
    begin
    SELECT tmp_counter1.NEXTVAL into counter1 from dual;
    insert into TMP_TABLE2 (COL1, COL2)
    values
    (ld.COL1, counter1);
    end;
    /

    insert into tmp_table1 values (1);
    insert into tmp_table1 values (2);
    insert into tmp_table1 values (3);
    insert into tmp_table1 values (1);
    insert into tmp_table1 values (2);

    delete from tmp_table1 where col1=1;

    select * from tmp_table2;
    --------
    This gives me two different numbers in COL2 (1 and 2) on the two rows deleted - but I needed the same number to be able to identify which rows that was deleted in one command. How do I do that? I have not had any success by trying to declare the variable before the "FOR-EACH-ROW-part".

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I think you need 2 triggers and a package to do this. The package exists merely to hold a variable for the counter number:
    Code:
    create or replace package my_package is
      counter INTEGER;
    end;
    /
    The first trigger is statement level (not "for each row") and does this:
    Code:
    CREATE TRIGGER tmp_trigger1 before delete on tmp_table1
    begin
      SELECT tmp_counter1.NEXTVAL into my_package.counter from dual;
    end;
    /
    The second fires for each row and uses the counter value set by the first:
    Code:
    CREATE TRIGGER tmp_trigger2 before delete on tmp_table1
    for each row
    begin
      insert into TMP_TABLE2 (COL1, COL2)
      values
      (:old.COL1, my_package.counter1);
    end;
    /

  3. #3
    Join Date
    Jun 2003
    Posts
    81

    Thanks

    Thank you - that solved the problem!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •