-- (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:
--
-- db2 -td@ -vf example1.db2
--
-- This script demonstrates how to write triggers which do not activate
-- for specific users
--
-- NOTE: You likely have to change user ID's and passwords for this example to work.


CONNECT TO TWOPART USER db2admin
@

CREATE TABLE db2admin.t1 (c1 int)
@
CREATE TABLE db2admin.t2 (c1 int)
@

-- Note: You may have to grant proper privileges to administrator
--       Uncomment as needed.
-- GRANT SELECT, INSERT ON db2admin.t1 TO USER administrator@
-- GRANT SELECT, INSERT ON db2admin.t2 TO USER administrator@


CREATE TRIGGER db2admin.trig1
AFTER INSERT ON db2admin.T1
REFERENCING NEW AS o 
FOR EACH ROW MODE DB2SQL
WHEN (USER <> 'ADMINISTRATOR')
BEGIN ATOMIC
	INSERT INTO db2admin.t2 values (o.c1);
END
@


INSERT INTO db2admin.t1 values (111)
@

SELECT * FROM db2admin.t2
@

CONNECT TO TWOPART USER administrator
@

INSERT INTO db2admin.t1 values (222)@
SELECT * FROM db2admin.t2@

-- cleanup
CONNECT TO TWOPART USER db2admin@
drop trigger db2admin.trig1@
drop table db2admin.t1@
drop table db2admin.t2@