Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41

    Unhappy Unanswered: Calling a Stored Proc from a Trigger

    Hi

    Is there a special Syntax used to call a Proc from a Trigger?

    I have the following.

    In my trigger, I want to disable another 2 triggers. When I tried to do this, I got a Cannot Commit in Trigger which is fine, I decided to go the Stored Proc root but I just cant seem to get it working.

    here is my Code

    -----------------------------------

    CREATE OR REPLACE TRIGGER "SYSADM"."TRIG_STOP" BEFORE INSERT OR UPDATE OF "ANTOMK", "EKOBOKF", "EKOTXT",
    "EXTRAUTB", "FELNR", "GENLART", "LONTXT", "STATUS", "UTBETDAT", "LONUPD", "OID",
    "POSTBANK", "REGDAT", "REGSIGN", "REGTID" ON
    "OA0P0021" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
    DECLARE

    V_GTOM NUMBER (13);

    BEGIN
    IF INSERTING THEN
    IF :NEW.STATUS = 500 OR :NEW.STATUS = 520 THEN
    exec DISABLE_TRIGGER;
    ELSE
    exec ENABLE_TRIGGER;
    END IF;
    END IF;

    IF UPDATING THEN

    IF :NEW.STATUS = 500 OR :NEW.STATUS = 520 THEN
    exec DISABLE_TRIGGER;
    ELSE
    exec ENABLE_TRIGGER;
    END IF;
    END IF;
    END;

    /
    --Enable Trigger
    CREATE OR REPLACE PROCEDURE ENABLE_TRIGGER
    IS
    BEGIN
    EXECUTE IMMEDIATE 'ALTER TRIGGER SYSADM.TRIG_OS0P0142 ENABLE';
    EXECUTE IMMEDIATE 'ALTER TRIGGER SYSADM.TRIG_OSOP0120 ENABLE';
    END;
    /

    -- Disable Trigger
    CREATE OR REPLACE PROCEDURE DISABLE_TRIGGER
    IS
    BEGIN
    EXECUTE IMMEDIATE 'ALTER TRIGGER SYSADM.TRIG_OS0P0142 DISABLE';
    EXECUTE IMMEDIATE 'ALTER TRIGGER SYSADM.TRIG_OSOP0120 DISABLE';
    END;
    /

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The call procedure syntax would be:
    Code:
    IF :NEW.STATUS = 500 OR :NEW.STATUS = 520 THEN
      DISABLE_TRIGGER;
    ELSE
      ENABLE_TRIGGER;
    END IF;
    (No EXEC keyword in PL/SQL).

    But your procedure will not work, because it executes DDL, which causes an implicit COMMIT, which is not allowed. Yes, you could use AUTONOMOUS_TRANSACTION, but this is still not what you want to do. Disabling a trigger disables it for everyone, not just the current transaction. Instead, you could set a global variable in a package (e.g. g_trigger_active := FALSE), and then have those other triggers check that value before doing anything.

  3. #3
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41
    Thanks Tony...

    I am still new to this whole thing. How would define a global variable?
    I have these 2 other triggers that I need to disable when a process starts cause it writes about 800 000 rows which has changed my processing time from 3 hours to 3 days.

    Can you please help me here? Just to set up these variable and possibly just a little explanation of what it does.

    Thanks

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As Tony already said, you should define a global variable in a package, such as:
    Code:
    create or replace package pkg_status
    as
       g_trigger_active boolean;
    end pkg_status;
    Then, code in a trigger would be something like
    Code:
    IF :NEW.STATUS = 500 OR :NEW.STATUS = 520 THEN
      pkg_status.g_trigger_active := 'FALSE';
    ELSE
      pkg_status.g_trigger_active := 'TRUE';
    END IF;
    Furthermore, the procedure would check variable value and act according to it:
    Code:
    create or replace procedure prc_status
    as
    begin
       ...
       if pkg_status.g_trigger_active = 'FALSE' then
          null;   /* do nothing */
       else
          ... /* execute code of your procedure */
       end if;
    end;
    Perhaps it would be a good idea to include all of the procedures that deal with the problem into the same package.

  5. #5
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41
    Thank you, it worked very well...

Posting Permissions

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