Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    3

    Red face Unanswered: need help in creating simple trigger

    hi there,
    I want to create a trigger before delete a row from one table --> move the data to another (history) table.
    This is the trigger:

    CREATE OR REPLACE TRIGGER move_t_H
    BEFORE DELETE
    ON GAMES
    FOR EACH ROW
    BEGIN
    find_game_for_history(:OLD.G_CODE);
    END;
    and i get the following messages that dont allow me to do the tasks in the procedures
    This is the procedure:


    CREATE OR REPLACE PROCEDURE find_game_for_history(game_num IN number DEFAULT null)
    AS
    game_var foot.games%ROWTYPE;
    BEGIN
    select *
    INTO game_var
    from games
    where (G_CODE=game_num);
    DBMS_OUTPUT.PUT_LINE
    (game_var.TEAM_A || ' '||game_var.TEAM_B);
    add_game_to_history
    (game_var.G_CODE , game_var.CYCLE_CODE ,game_var.TEAM_A ,
    game_var.TEAM_B ,game_var.REF_MAIN ,game_var.REF_A ,game_var.REF_B ,game_var.REF_C, game_var.FIELD_ID);
    END;
    /


    any one knows what is wrong? how should i do that?
    Thank you all!!
    Attached Thumbnails Attached Thumbnails msg.jpg  

  2. #2
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Re: need help in creating simple trigger

    Originally posted by arik620
    hi there,
    I want to create a trigger before delete a row from one table --> move the data to another (history) table.
    This is the trigger:

    CREATE OR REPLACE TRIGGER move_t_H
    BEFORE DELETE
    ON GAMES
    FOR EACH ROW
    BEGIN
    find_game_for_history(:OLD.G_CODE);
    END;
    and i get the following messages that dont allow me to do the tasks in the procedures
    This is the procedure:


    CREATE OR REPLACE PROCEDURE find_game_for_history(game_num IN number DEFAULT null)
    AS
    game_var foot.games%ROWTYPE;
    BEGIN
    select *
    INTO game_var
    from games
    where (G_CODE=game_num);
    DBMS_OUTPUT.PUT_LINE
    (game_var.TEAM_A || ' '||game_var.TEAM_B);
    add_game_to_history
    (game_var.G_CODE , game_var.CYCLE_CODE ,game_var.TEAM_A ,
    game_var.TEAM_B ,game_var.REF_MAIN ,game_var.REF_A ,game_var.REF_B ,game_var.REF_C, game_var.FIELD_ID);
    END;
    /


    any one knows what is wrong? how should i do that?
    Thank you all!!
    You can't select the table your trigger are on. Try to use :OLD to passing values to add_game_to history

    Hope this will help you

  3. #3
    Join Date
    Apr 2003
    Posts
    4
    CREATE OR REPLACE TRIGGER move_t_H
    BEFORE DELETE
    ON GAMES
    FOR EACH ROW
    BEGIN
    add_game_to_history
    (:OLD.G_CODE , :OLD.CYCLE_CODE ,:OLD.TEAM_A ,
    :OLD.TEAM_B ,:OLD.REF_MAIN ,:OLD.REF_A ,:OLD.REF_B ,:OLD.REF_C, :OLD.FIELD_ID);
    END;


    I Think this has to work

  4. #4
    Join Date
    May 2003
    Posts
    3

    Smile

    Thanks too you two,
    I'll try it right away and update that message if it worked or not !!!!

    Thanks

Posting Permissions

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