Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136

    Unanswered: Trigger to alter trigger...help

    I need to put up a trigger on a table CONFIG, such that if field TOOL_OPEN is 'Y' then it should enable selected 5 triggers on diff tables and if 'N' then disable the triggers.

    Below code gives ....'ALTER' is not a valid identifier.... error, can anybody please tell me where am I wrong?

    ================================================== ==
    CREATE OR REPLACE TRIGGER MYDB.TRIG_ENA_DIS
    AFTER UPDATE
    ON MYDB.CONFIG
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
    v_errmsg VARCHAR2 (100);
    BEGIN
    IF (:NEW.tool_open = 'Y')
    THEN
    ALTER TRIGGER GRP_ACTIVE_TRIG ENABLE;
    END IF;
    EXCEPTION
    WHEN OTHERS
    THEN
    v_errmsg := SUBSTR (SQLERRM, 1, 100);
    raise_application_error (-20010,
    'Trigger TRIG_ENA_DIS resulted IN error - '
    || v_errmsg
    );
    RAISE;
    END TRIG_ENA_DIS;

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    EXECUTE IMMEDIATE 'ALTER TRIGGER ...';
    HOWEVER, I wouldn't recommend doing this. When you disable a trigger, you disable it for everyone - not just the current session.

    A better approach would be to create a variable in a PACKAGE SPEC that everyone can access, and set that value:

    Code:
    CREATE OR REPLACE TRIGGER MYDB.TRIG_ENA_DIS
    AFTER UPDATE
    ON MYDB.CONFIG 
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
    v_errmsg VARCHAR2 (100);
    BEGIN
    IF (:NEW.tool_open = 'Y')
    THEN
      PUBLIC_PACKAGE.SET_TRIG_CHK := 'TRIG_ENA_DIS';
    END IF;
    
    END TRIG_ENA_DIS;
    In your other TRIGGERS have code like:

    Code:
    If PUBLIC_PACKAGE.SET_TRIG_CHK := 'TRIG_ENA_DIS'
    THEN
      RETURN;
    END IF;
    -cf

  3. #3
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    thanks for your quick help.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Probably should also actually create some interfacing procedures and functions:

    SET_VALUE()
    GET_VALUE()

    instead of updating the variable directly.

    -Chuck

  5. #5
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Also does this line have any purpose?

    Code:
    REFERENCING NEW AS NEW OLD AS OLD

Posting Permissions

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