Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002
    Location
    Calgary, Canada
    Posts
    30

    Unanswered: Triggers, procedures and inserting into a table

    hello

    I have a table that currently has a trigger on it.

    Code:
    CREATE trigger update_host
        BEFORE UPDATE ON host FOR EACH row
    		EXECUTE PROCEDURE host_hist_ins;
    What I want to happen is this:

    When the trigger goes off, it takes the current record that is being updated from the host table, and insert it into the host_hist table.

    The problem I am having is how do I write it so it is parsing the value of the ID (Primary key) into the code ?

    Code:
    ...
    DECLARE
      cursor a_cur IS SELECT host_id FROM host where id = [this is the variable I want from the update]   
    BEGIN
    ...

    Hopefully that makes sense

    thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In the trigger, you will have to pass the ID to the procedure. The ID will be :NEW.ID

    CREATE trigger update_host
    BEFORE UPDATE ON host FOR EACH row
    BEGIN
    host_hist_ins( :NEW.ID );
    END;

    The procedure requires a parameter to receive the value:

    PROCEDURE host_hist_ins ( p_id IN host.id%TYPE ) IS
    BEGIN
    ...
    END;

  3. #3
    Join Date
    Apr 2002
    Location
    Calgary, Canada
    Posts
    30
    heh after some reading I found the :new and ld :-) I have to get my mind out of parsing variables to web pages.

    thanks for the help.

Posting Permissions

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