Enable & Disable trigger in db2

Question

I am trying to enable and disable trigger in db2.

The command I run : "ALTER TRIGGER CASE_STATUS_CHANGED DISABLE"

And the error is "An unexpected token "DISABLE" was found following "CASE_STATUS_CHANGED". Expected tokens may include: "SECURED".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.18.60"

Can anyone enlight me how to enable and disable the trigger in db2?

Answer 1

DB2 LUW unfortunately does not allow to disable triggers. You can only DROP a trigger.

There is an ALTER TRIGGER statement. It is the reason for getting that specific error because the statement can be used to switch a trigger from being NOT SECURED to SECURED and back.

Answer 2

Late to the party but wanted to show the trick I use to disable a trigger.

I define a SMALLINT variable that acts as a flag and then the first thing the trigger does is check the variable value. If equal to zero, then it's considered disabled and the trigger ends.

create variable mytrigger1_enabled smallint default 1;

create or replace trigger mytrigger1
before insert or delete or update on employee for each row
begin
  if mytrigger1_enabled <> 0
    then
      -- do tasks
  end if;
end//

-- now, disable the trigger
set mytrigger1_enabled = 0;

-- now, enable it back
set mytrigger1_enabled = 1;

I know it requires more work that you probably wanted but to me it gets the job done.

Answer 3

I defined one table and two stored procedures to enable and disable triggers. First create the objects then CALL the SET_TRIGGER_OFF and SET_TRIGGER_ON procedures whenever you need.

CREATE TABLE SUSPENDED_TRIGGER (
  ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ),
  TRIGNAME VARCHAR(30) NOT NULL UNIQUE,
  TRIGTEXT CLOB (2M) NOT NULL,
  SUSPENDTIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
  PRIMARY KEY (ID)
)

CREATE PROCEDURE SET_TRIGGER_OFF (IN TRIGNAME1 VARCHAR(30))
  LANGUAGE SQL
  MODIFIES SQL DATA
BEGIN ATOMIC
  DECLARE STMT1 CLOB (2M);
  IF EXISTS(SELECT TEXT FROM SYSCAT.TRIGGERS WHERE TRIGNAME=TRIGNAME1) THEN
    INSERT INTO SUSPENDED_TRIGGER (TRIGNAME, TRIGTEXT)
    SELECT TRIGNAME, TEXT FROM SYSCAT.TRIGGERS WHERE TRIGNAME=TRIGNAME1;
    SET STMT1 = 'DROP TRIGGER ' || TRIGNAME1;
    PREPARE S1 FROM STMT1;
    EXECUTE S1;
  END IF;
END

CREATE PROCEDURE SET_TRIGGER_ON (IN TRIGNAME1 VARCHAR(30))
  LANGUAGE SQL
  MODIFIES SQL DATA
BEGIN ATOMIC
  DECLARE STMT1 CLOB (2M);
  IF EXISTS(SELECT TRIGTEXT FROM SUSPENDED_TRIGGER WHERE TRIGNAME=TRIGNAME1) THEN
    SET STMT1 = (SELECT TRIGTEXT FROM SUSPENDED_TRIGGER WHERE TRIGNAME=TRIGNAME1);
    PREPARE S1 FROM STMT1;
    EXECUTE S1;
    DELETE FROM SUSPENDED_TRIGGER WHERE TRIGNAME=TRIGNAME1;
  END IF;
END