-- (c) Copyright IBM Corp. 2002  All rights reserved.                 
--                                                                    
-- This sample program is owned by International Business Machines    
-- Corporation or one of its subsidiaries ("IBM") and is copyrighted  
-- and licensed, not sold.                                            
--                                                                    
-- You may copy, modify, and distribute this sample program in any    
-- form without payment to IBM,  for any purpose including developing,
-- using, marketing or distributing programs that include or are      
-- derivative works of the sample program.                            
--                                                                    
-- The sample program is provided to you on an "AS IS" basis, without 
-- warranty of any kind.  IBM HEREBY  EXPRESSLY DISCLAIMS ALL         
-- WARRANTIES EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO
-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTIC-
-- ULAR PURPOSE. Some jurisdictions do not allow for the exclusion or 
-- limitation of implied warranties, so the above limitations or      
-- exclusions may not apply to you.  IBM shall not be liable for any  
-- damages you suffer as a result of using, modifying or distributing 
-- the sample program or its derivatives.                             
--                                                                    
-- Each copy of any portion of this sample program or any derivative  
-- work,  must include a the above copyright notice and disclaimer of 
-- warranty.                                                          


--
-- To Run this script, connect to the database and type:
--
-- db2 -td@ -vf example3.db2
--
-- This script creates all the objects required for disabling
-- and eneabling triggers using stored procedures.
-- 
-- NOTE: You may have to change the path for the table space container path
--       below


--DROP PROCEDURE TRIGTOOL.SHOW_DISABLED_TRIGGERS ()@
--DROP PROCEDURE TRIGTOOL.ENABLE_TRIGGER (VARCHAR (128),VARCHAR(128))@
--DROP PROCEDURE TRIGTOOL.DISABLE_TRIGGER (VARCHAR(128),VARCHAR(128))@
--DROP TABLE TRIGTOOL.DISABLED_TRIGGERS@
--DROP TABLESPACE TS32K@
--DROP BUFFERPOOL BP32K@

CREATE BUFFERPOOL BP32K SIZE 1000 PAGESIZE 32K@

CREATE TABLESPACE TS32K PAGESIZE 32K
MANAGED BY SYSTEM USING ('c:\ts32k\') BUFFERPOOL BP32K@


-- TRIGTOOL.DISABLED_TRIGGERS stores temporarily disabled triggers
CREATE TABLE TRIGTOOL.DISABLED_TRIGGERS (
	TRIGSCHEMA VARCHAR(128) not null,
	TRIGNAME VARCHAR(128) not null,
	TABSCHEMA VARCHAR(128) not null,
	TABNAME VARCHAR(128) not null,
	QUALIFIER VARCHAR(128) not null,
	FUNC_PATH VARCHAR(254) not null,
	TEXT VARCHAR(31500) not null
	) in TS32K
@

ALTER TABLE TRIGTOOL.DISABLED_TRIGGERS 
   ADD CONSTRAINT disabledtrig_pk PRIMARY KEY (trigschema,trigname)@


CREATE PROCEDURE TRIGTOOL.SHOW_DISABLED_TRIGGERS (  )
LANGUAGE SQL
RESULT SETS 1
BEGIN
DECLARE c_triggers CURSOR WITH RETURN FOR
    SELECT trigschema, trigname FROM TRIGTOOL.DISABLED_TRIGGERS;

OPEN c_triggers;

END@

CREATE PROCEDURE TRIGTOOL.DISABLE_TRIGGER (
    IN v_schema VARCHAR(128),
    IN v_name VARCHAR(128))
SPECIFIC DISABLE_TRIGGER
LANGUAGE SQL
BEGIN
    DECLARE SQLCODE INT DEFAULT 0;
    DECLARE v_stmt VARCHAR(250);

    DECLARE EXIT HANDLER FOR NOT FOUND
        SIGNAL SQLSTATE '80000' SET MESSAGE_TEXT='Trigger Not Found';

    DECLARE EXIT HANDLER FOR SQLWARNING
        SIGNAL SQLSTATE '80001' SET MESSAGE_TEXT='Unable to disable trigger';

    INSERT INTO TRIGTOOL.DISABLED_TRIGGERS
    SELECT
	TRIGSCHEMA, TRIGNAME, TABSCHEMA, TABNAME,
	QUALIFIER, FUNC_PATH, CAST(TEXT as VARCHAR(31500))
    FROM SYSCAT.TRIGGERS
    WHERE TRIGSCHEMA = v_schema and TRIGNAME = v_name
    AND VALID='Y';

    SET v_stmt =  'DROP TRIGGER ' || v_schema || '.' ||v_name;
    EXECUTE IMMEDIATE v_stmt;
    
END
@



CREATE PROCEDURE TRIGTOOL.ENABLE_TRIGGER (
    IN v_schema VARCHAR(128), 
    IN v_name VARCHAR(128))
LANGUAGE SQL
BEGIN

DECLARE SQLCODE INT DEFAULT 0;

DECLARE v_qualifier VARCHAR(128);
DECLARE v_func_path VARCHAR(1000);
DECLARE v_stmt VARCHAR(32672);

DECLARE v_curr_qualifier VARCHAR(128);
DECLARE v_curr_funcpath VARCHAR(1000);


DECLARE EXIT HANDLER FOR SQLWARNING
    SIGNAL SQLSTATE '80000' 
    SET MESSAGE_TEXT = 'Error. Manual recreation required';

SET v_curr_qualifier = CURRENT SCHEMA;
SET v_curr_funcpath = CURRENT FUNCTION PATH;

SELECT qualifier, func_path, TEXT into v_qualifier, v_func_path, v_stmt
FROM TRIGTOOL.DISABLED_TRIGGERS
    WHERE trigschema=v_schema and trigname=v_name;

SET v_func_path = 'SET CURRENT FUNCTION PATH = ' || v_func_path;
EXECUTE IMMEDIATE v_func_path;

SET v_qualifier = 'SET CURRENT SCHEMA = ' || v_qualifier;
EXECUTE IMMEDIATE v_qualifier;

EXECUTE IMMEDIATE v_stmt;

DELETE FROM TRIGTOOL.DISABLED_TRIGGERS
    WHERE trigschema=v_schema and trigname=v_name;

SET v_curr_qualifier = 'SET SCHEMA = ' || v_curr_qualifier;
SET v_curr_funcpath = 'SET CURRENT FUNCTION PATH = ' || v_curr_funcpath;
EXECUTE IMMEDIATE v_curr_qualifier;
EXECUTE IMMEDIATE v_curr_funcpath;

END
@