Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Unanswered: Triggers calling procedures

    I have created both a trigger and a procedure.
    The idea behind the trigger is to call the procedure based on the criteria in the trigger.

    The trigger is simple; when a particular event occurs it fires off. From the Oracle table that this event occurred, I'd like to pass a particular column to the procedure.

    I have tested the procedure separately, and know that it is working, but the trigger does not seem to be working. Can anyone help me figure out what how to call the procedure from the trigger and pass a parameter to the procedure.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Triggers calling procedures

    It is a simple process. Post your code so someone can point out what's wrong with it.

  3. #3
    Join Date
    Mar 2004
    Posts
    2
    CREATE OR REPLACE TRIGGER trig_name
    AFTER
    INSERT OR UPDATE
    ON oracle_table
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    WHEN (new.cd = 'event1' AND new.cd Is Not Null)
    DECLARE

    Begin

    procedure(:new.column_to_pass);

    End;

    -- I've been talking to a couple of people about it and someone had mentioned that you can't have commits in your procedure; is that true? And any suggestions on this code are greatly appreciated.
    Thanks

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    That is correct, you cannot commit within a trigger except in an autonomous transaction, which is very rarely what you want (it is only really suitable for auditing failed DML). In fact, it is bad practice to commit within stored procedures for this kind of reason: it limits the usability of the procedure. Commiting (or rolling back) should be done by the caller of the procedure, which may want to do other things besides call that procedure before committing (like you do here).

Posting Permissions

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