Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Unanswered: Trigger that calls a sp

    I have a trigger that is calling a stored proc and producing an error.
    Here is the trigger:
    TRIGGER SCHEMA.TRIGGERNAME
    AFTER INSERT ON SCHEMA.TABLE
    FOR EACH ROW
    WHEN (NEW.INVOICENUM IS NULL)
    BEGIN
    Call StoredProc('04 12053');

    END;

    I plan to change the trigger to pass a variable into the proc but am testing it using one record for now.
    The error message I get is:
    PLS-00103: Encountered the symbol "StoredProc" when expecting one of the following:

    := . ( @ % ;
    The symbol ":=" was substituted for "StoredProc" to continue.

    If I call the proc from a SQL Edit window using the same params, it works fine.

    Any thoughts?

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    Call can only be used inside of SQL*PLUS - it has no meaning outside of that application.

    In a trigger, take out the called command, and just leave the procedure and params there.
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Oct 2004
    Posts
    4
    Ok, I did try that, but then I have a problem with Commits and Rollbacks in the stored proc. It seems that the application isnt responding well to that. Do I need to put the commit into the Trigger or the Procedure?

  4. #4
    Join Date
    Oct 2004
    Posts
    9
    You may use "call" statement withing a "create trigger" one, without using the "begin" - "end" directives.
    i.e.
    Code:
    CREATE OR REPLACE TRIGGER SCHEMA.TRIGGER_NAME
    AFTER INSERT ON SCHEMA.TABLE
    FOR EACH ROW
    WHEN (NEW.INVOICENUM IS NULL)
      Call StoredProc('04 12053')
    /
    About commits and rollbacks. They are not allowed within a trigger context. If your application (business logic) requires them, think of implementing autonomous transactions from stored procedures or functions. Be very carefull though when using autonomous transactions. Be also aware of the "mutating trigger" ORA error.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Neither: put the commit in the application that inserted the row.

  6. #6
    Join Date
    Oct 2004
    Posts
    4
    Quote Originally Posted by parisss
    You may use "call" statement withing a "create trigger" one, without using the "begin" - "end" directives.
    i.e.
    Code:
    CREATE OR REPLACE TRIGGER SCHEMA.TRIGGER_NAME
    AFTER INSERT ON SCHEMA.TABLE
    FOR EACH ROW
    WHEN (NEW.INVOICENUM IS NULL)
      Call StoredProc('04 12053')
    /
    About commits and rollbacks. They are not allowed within a trigger context. If your application (business logic) requires them, think of implementing autonomous transactions from stored procedures or functions. Be very carefull though when using autonomous transactions. Be also aware of the "mutating trigger" ORA error.
    Thanks Pariss. The begin and end directives helped..

  7. #7
    Join Date
    Oct 2004
    Posts
    4
    Guys,

    Today I ran into another problem with my trigger (which, after testing, I enhanced to include variables etc)
    Here is the trigger today:

    TRIGGER SCHEMA.TRIGGER
    AFTER INSERT ON SCHEMA.TABLE
    FOR EACH ROW
    BEGIN
    IF (:NEW.INVOICENUM IS NULL) THEN
    :NEW.DIAGNCODE4 := 'NO';
    ELSE
    :NEW.DIAGNCODE4 := 'YES';
    END IF;
    END;

    This produces the following Error:
    ORA-04084: cannot change NEW values for this trigger type.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by mrchunk
    Guys,

    Today I ran into another problem with my trigger (which, after testing, I enhanced to include variables etc)
    Here is the trigger today:

    TRIGGER SCHEMA.TRIGGER
    AFTER INSERT ON SCHEMA.TABLE
    FOR EACH ROW
    BEGIN
    IF (:NEW.INVOICENUM IS NULL) THEN
    :NEW.DIAGNCODE4 := 'NO';
    ELSE
    :NEW.DIAGNCODE4 := 'YES';
    END IF;
    END;

    This produces the following Error:
    ORA-04084: cannot change NEW values for this trigger type.
    Of course you can't. You are telling the database to make a change to a row AFTER it has written it. You should use a "BEFORE INSERT" instead of "AFTER INSERT" and everything will work fine.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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