-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- @@@ END COPYRIGHT @@@
-- setting the 'NEW" transition variable is a before trigger corresponding to a clustring/primary key

------------------------------------------------------------------
-- SET ENVIRONMENT
------------------------------------------------------------------


obey TEST_13_4(clean_up);
obey TEST_13_4(set_up);
log   LOG_13_4 clear;
obey TEST_13_4(tests);
LOG;
obey TEST_13_4(clean_up);
exit;

?section clean_up
set schema CAT1.SCHM;
DROP TRIGGER btrig1;
DROP TRIGGER btrig2;
DROP TRIGGER btrig3;
DROP TRIGGER btrig4;
DROP TRIGGER btrig5;
DROP TABLE cat1.schm.tab;
------------------------------------------------------------------


?section set_up
set schema cat1.schm;
CREATE TABLE tab (a INT NOT NULL, b INT, c INT, d INT NOT NULL NOT DROPPABLE, PRIMARY KEY (a,d) NOT DROPPABLE)
	STORE BY (a,d);	

---------------------------------
-- DEFINE TRIGGER btrig1
---------------------------------

CREATE TRIGGER btrig1
BEFORE INSERT
ON tab
REFERENCING NEW AS newR
FOR EACH ROW
SET newR.a = newR.b+newR.c;

---------------------------------
-- DEFINE TRIGGER btrig2
---------------------------------

CREATE TRIGGER btrig2
BEFORE INSERT
ON tab
REFERENCING NEW AS newR
FOR EACH ROW
SET newR.d = newR.a+newR.d;



?section tests

------------------------------------------------------------------
--          TEST CASE
------------------------------------------------------------------

INSERT INTO tab VALUES (1,2,3,4); -- should actually insert (5,2,3,9)
INSERT INTO tab VALUES (2,3,4,5); -- should actually insert (7,3,4,12)

-- tab should contain:
-- (5,2,3,9)
-- (7,3,4,12)
SELECT * FROM tab order by a; -- check result


---------------------------------
-- DEFINE TRIGGER btrig3
---------------------------------

CREATE TRIGGER btrig3
BEFORE UPDATE OF (b)
ON tab
REFERENCING NEW AS newR, OLD AS oldR
FOR EACH ROW
SET newR.c = oldR.b;

UPDATE tab SET B=10;

-- tab should contain:
-- (5,10,2,9)
-- (7,10,3,12)
SELECT * FROM tab order by a; -- check result 


-- the following ddl should fail:

CREATE TRIGGER btrig4
BEFORE UPDATE OF (b)
ON tab
REFERENCING NEW AS newR, OLD AS oldR
FOR EACH ROW
SET newR.a = oldR.b; 

-- the following ddl should fail:

CREATE TRIGGER btrig5
BEFORE UPDATE OF (c)
ON tab
REFERENCING NEW AS newR, OLD AS oldR
FOR EACH ROW
SET newR.d = oldR.b; 

------------------------------------------------------------------
--           END
------------------------------------------------------------------
