#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    1
    Rep Power
    0

    Dynamic insertion trigger


    Hi, I'm pulling my hair out with this problem. I'm trying to make a trigger that, upon an update in one table and after checking that update, the trigger will insert a new tuple into a different table, here's my command:

    create or replace trigger release_date_news_trigger
    after update of release_date on games
    referencing old as oldtuple
    new as newtuple
    for each row
    when(oldtuple.release_date < newtuple.release_date)
    begin
    insert into news(news#, games#, news_article, date_posted) values(news_pk_sequence.nextval, newtuple.game#, 'Game delayed!', sysdate);
    end release_date_news_trigger;
    /

    but it 'compiles with errors. here are the erors:

    Errors for TRIGGER RELEASE_DATE_NEWS_TRIGGER:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    2/1 PL/SQL: SQL Statement ignored
    2/25 PLS-00390: undefined column 'GAMES#' in INSERT statement

    Anyone able to help, and prevent my heart attack, or my computer meeting an unfortunate fate!

    Cheers
  2. #2
  3. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    12
    You are using newtuple.game# in your insert statement that is not declared in your table structure, use newtuple.games# as follows:

    insert into news(news#, games#, news_article, date_posted) values(news_pk_sequence.nextval, newtuple.games#, 'Game delayed!', sysdate);

    Save your heart to be break.

    Regards

IMN logo majestic logo threadwatch logo seochat tools logo