-- (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 example5.db2
--
-- This script demonstrates the complex case for disabling and enabling of triggers.
--
-- When executing, we purposely change the schema and current function path to build 
-- a trigger. The trigger makes references to unqualified tables and functions.
-- The stored procedures are able to properly recreate the table nonetheless.

-- change schema to something other than DEFAULT schema
SET SCHEMA XYZ@

-- change function path to something other than default function path
SET CURRENT FUNCTION PATH = SYSIBM,SYSFUN,SYSPROC,ABC@


CREATE TABLE t1 (c1 int)@
CREATE TABLE t2 (trigger_fired CHAR(1))@

CREATE FUNCTION ABC.f1()
RETURNS VARCHAR(1)
RETURN (SELECT IBMREQD FROM SYSIBM.SYSDUMMY1)@


-- NOTE the unqualified reference to t2 and function f1()
CREATE TRIGGER trig1
AFTER INSERT ON T1
REFERENCING NEW AS o 
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
	INSERT INTO t2 values (f1());
END@

-- After inserting a value into t1, trigger will activate and insert 'Y' into T2
-- TRIGGER SHOULD FIRE
INSERT INTO t1 values (1)@
SELECT * FROM T2@

-- Clean Up T2
DELETE FROM T2@

-- DISABLE TRIGGER:
CALL TRIGTOOL.DISABLE_TRIGGER('XYZ','TRIG1')@
CALL TRIGTOOL.SHOW_DISABLED_TRIGGERS()@

-- TRIGGER SHOULD NOT FIRE
INSERT INTO t1 values (1)@
SELECT * FROM T2@


-- before restoring, change the schema and function path once again 
SET SCHEMA JKL@
SET CURRENT FUNCTION PATH = SYSIBM,SYSFUN,SYSPROC@

-- ENABLE TRIGGER:
CALL TRIGTOOL.ENABLE_TRIGGER('XYZ','TRIG1')@

SET SCHEMA XYZ@

-- TRIGGER SHOULD FIRE
INSERT INTO t1 values (1)@
SELECT * FROM T2@

-- NO MORE DIASBLED TRIGGERS
CALL TRIGTOOL.SHOW_DISABLED_TRIGGERS()@

-- cleanup

drop trigger xyz.trig1@
drop function abc.f1()@
drop table xyz.t1@
drop table xyz.t2@