Results 1 to 4 of 4

Thread: Trigger error

  1. #1
    Join Date
    Oct 2004
    Posts
    8

    Question Unanswered: Trigger error

    I have added a trigger to an existing table "NewTable"
    The trigger code is as
    Code:
    create or replace trigger "TRIGGER_NewTable" 
    after insert or delete or update on "NewTable" 
    declare pgm v$session.PROGRAM%type;
    begin
    select PROGRAM into pgm from v$session where sid = (select distinct sid from v$mystat) 
           and program like 'app_name';
    exception
     when NO_DATA_FOUND thne
     insert into "COM_Warning" values ( N'NewTable' );
    end;
    but it shows compilation errors, and when I remove the exception handling

    Code:
    create or replace trigger "TRIGGER_NewTable" 
    after insert or delete or update on "NewTable" 
    declare pgm v$session.PROGRAM%type;
    begin
    select PROGRAM into pgm from v$session where sid = (select distinct sid from v$mystat) 
           and program like 'app_name';
    end;
    That's compile success.
    But I cannot find any error in the exception handling code.
    Why does it do?

    P.S. Select on v_$session and v_$mystat is granted to.
    Last edited by wildchoi; 11-19-04 at 22:52. Reason: implement

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    And the compilation errors were...?

    > when NO_DATA_FOUND thne
    might be the problem.

    If not, what version of Oracle are you using? The text literal syntax requires 9i AFAIK. Does the INSERT work on its own on the command line?

    I assume there is a valid table, view or synonym "COM_Warning" (case sensitive name) in scope? Bad luck having to work with those double-quoted mixed-case names btw.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Moreover, is this syntax OK? ... values ( N'NewTable' ); What if you put it as ... values ( 'NewTable' ); (omit "N")?

  4. #4
    Join Date
    Oct 2004
    Posts
    8
    Thank you for the replies.
    Now the problem is solved.
    Just because the table "COM_Warning" is built in case-insensitive
    and inserting with case-sensitive name.

Posting Permissions

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