Fire Trigger
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Fire Trigger

  1. #1
    Join Date
    Apr 2006
    Posts
    9

    Fire Trigger

    I have a trigger that inserts a new value into a field after a row is commited. Is there a way I could create an insert triggers that inserts that number generated by the trigger into another field on the same row eg:
    tblX has fields ID, ProgessID, Service
    When ID is populated by the trigger and assigned a value say 10, that value is also automatically assigned to ProgessID ie ProgressID = 10 as well

    Thank you

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool


    Try adding this to your trigger:
    Code:
    ...
      :NEW.ProgessID:=:NEW.ID;
    ...
    PS: It has to be a BEFORE...trigger.


    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Apr 2006
    Posts
    9
    Thank you. It is a before trigger and the trigger body that assigns a value to PSID is
    BEGIN
    SELECT .nextval
    INTO :new.PSID
    FROM dual;
    END;

    So should I make it
    BEGIN
    SELECT SVDB.S_114_2_TBLPROGRESSNOTE.nextval
    INTO :NEW.ProgressID:=NEW.ID
    FROM dual;
    END;

  4. #4
    Join Date
    Apr 2006
    Posts
    9
    I tried it and I got error message
    Line # = 3 Column # = 23 Error Text = PL/SQL: ORA-01745: invalid host/bind variable name

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool


    No, you need two statement like this:
    Code:
    BEGIN 
      SELECT SVDB.S_114_2_TBLPROGRESSNOTE.nextval
        INTO :NEW.ID
        FROM dual;
      :NEW.ProgressID:=:NEW.ID;
    END;

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  6. #6
    Join Date
    Apr 2006
    Posts
    9
    Thank you LKBrown worked perfectly. Thats a new method I have learnt today . Please keep up the good work

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