Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    7

    Unanswered: dynamic insertion trigger - ALmost working!

    I'm trying to create a trigger that inserts a tuple into a table when a value in another table is altered, my code is below, however oracle sqlplus gives me a bad trigger definition error, here's the code:

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

    any ideas??

    Cheeres
    Last edited by smythst; 12-03-03 at 16:45.

  2. #2
    Join Date
    Sep 2003
    Posts
    27
    Even using the REFERENCING clause, I think you need colons before the reference tables.

    I.e.
    when(ldtuple.release_date < :newtuple.releasedate)...

    Good luck,

    Tim

  3. #3
    Join Date
    Dec 2003
    Posts
    7
    thanks, but am still getting the same problem, plus as it is, the command:

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

    doesn't terminate, I get the

    sql>command...
    2
    3
    etc, until I enter a /. Why is this? I positive the command is structured correctly, or does oracle treat it like a function?

    Thanks

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I've never understood why people use the REFERENCING clause. But anyway, within the WHEN clause you don't use colons but within the body you do (confusingly!).

    Actually the trigger code posted originally was invalid. It should look more like this:

    create trigger release_date_news_trigger
    after update of release_date on games
    referencing old as oldtuple new as newtuple -- No comma
    for each row
    when(oldtuple.release_date < newtuple.releasedate)
    begin
    insert into news( col1, col2, col3, col4 ) -- Always list columns
    values(news_pk_sequence.nextval, newtuple.game#, 'Game delayed!', sysdate)
    end;
    /

  5. #5
    Join Date
    Dec 2003
    Posts
    7
    ok it compiles now but with errors, can't think of why:

    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;
    /

    and then when I try an update to test the trigger it fails with a 'trigger is invalid ...' error.
    What I can't understand is why the compile error in the first place, it has to be some small syntax discrepency that I just can't see, anyone able to help?

    cheers.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Missing ";" here:

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

  7. #7
    Join Date
    Dec 2003
    Posts
    7
    thanks, still getting a compile error, but it's using the trigger and it appears to be working, might just capacity test the table, and see what errors (if any) crop up.

Posting Permissions

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