-- (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 example2.db2
--
-- This script demonstrates how to write triggers which can be disabled.


CREATE TABLE db2admin.t1 (c1 int)
@
CREATE TABLE db2admin.t2 (c1 int)
@
CREATE TABLE db2admin.trigger_state 
(
	trigschema VARCHAR(128) not null,
	trigname VARCHAR(30) not null, 
	active char(1) not null
)
@

CREATE UNIQUE INDEX db2admin.trigstateIX 
ON db2admin.trigger_state (trigschema, trigname) 
INCLUDE (active)
@


INSERT INTO db2admin.trigger_state VALUES ('DB2ADMIN','TRIG1','Y')
@
CREATE FUNCTION db2admin.trigger_enabled (
v_schema VARCHAR(128), 
v_name VARCHAR(30))
RETURNS VARCHAR(1)
RETURN (SELECT active FROM db2admin.trigger_state WHERE trigschema=v_schema and trigname=v_name)
@

CREATE TRIGGER db2admin.trig1
AFTER INSERT ON db2admin.T1
REFERENCING NEW AS o 
FOR EACH ROW MODE DB2SQL
WHEN (db2admin.trigger_enabled('DB2ADMIN','TRIG1') = 'Y')
BEGIN ATOMIC
	INSERT INTO db2admin.t2 values (o.c1);
END
@

INSERT INTO db2admin.t1 values (123)
@

SELECT * FROM db2admin.t2
@

UPDATE db2admin.trigger_state SET active='N' 
WHERE trigschema='DB2ADMIN' and trigname='TRIG1'
@
INSERT INTO db2admin.t1 values (456)@
SELECT * FROM db2admin.t2@

-- cleanup
drop trigger db2admin.trig1@
drop function db2admin.trigger_enabled@
drop table db2admin.t1@
drop table db2admin.t2@
drop table db2admin.trigger_state@