core/sql/regress/newregr/triggers/EXPECTED_2_3_2

>>obey TEST_2_3_2(tests);
>>
>>-- This env variable is set to enum value ALLOW_SPECIALTABLETYPE_SYNTAX,
>>-- which allows sqlci to use the "TEMP_TABLE(tmpTblName)" syntax.
>>set parserflags 1;

--- SQL operation complete.
>>
>>set schema cat1.schm;

--- SQL operation complete.
>>
>>create table t232A(
+>	a int not null not droppable, b int not null, c int not null,
+>	d int,
+>	constraint t232bplus check (b > 0), constraint t232ubc unique(b,c),
+>	primary key(a) not droppable)
+>	location $$partition3$$
+>	range partition(add first key  (100) location $$partition$$,
+>		  add first key  (200) location $$partition1$$,
+>		  add first key  (300) location $$partition2$$);

--- SQL operation complete.
>>
>>create table t232B (x int, y int, z int);

--- SQL operation complete.
>>
>>create trigger trg232 after update on t232A
+> referencing old as myold, new as mynew
+>  for each statement
+>    insert into t232B select a,b,c from mynew
+>        where mynew.d > (select MAX(d) from myold);

--- SQL operation complete.
>>
>>insert into t232A values (10,20,30,40),(110,120,130,140),(210,220,230,240),
+>	(310,320,330,340);

--- 4 row(s) inserted.
>>
>>update t232A set d=d+1;

--- 4 row(s) updated.
>>
>>update t232A set b=d+b;

--- 4 row(s) updated.
>>
>>update t232A set d=d-1;

--- 4 row(s) updated.
>>
>>showddl TABLE ( TEMP_TABLE t232A__TEMP);

CREATE TABLE (TEMP_TABLE CAT1.SCHM.T232A__TEMP)
  (
    "@UNIQUE_EXECUTE_ID"             CHAR(SIZE) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT -- NOT NULL NOT DROPPABLE
  , "@UNIQUE_IUD_ID"                 INT NO DEFAULT -- NOT NULL NOT DROPPABLE
  , A                                INT NO DEFAULT -- NOT NULL NOT DROPPABLE
  , B                                INT NO DEFAULT -- NOT NULL NOT DROPPABLE
  , C                                INT NO DEFAULT -- NOT NULL NOT DROPPABLE
  , D                                INT DEFAULT NULL
  , CONSTRAINT CAT1.SCHM.T232A__TEMP_266971761_2671 PRIMARY KEY
      ("@UNIQUE_EXECUTE_ID" ASC, "@UNIQUE_IUD_ID" ASC, A ASC) NOT DROPPABLE
  )
  LOCATION \NSK.$SYSTEM.ZSDZVD7K.D1SX7R00
  NAME NSK_SYSTEM_ZSDZVD7K_D1SX7R00
  RANGE PARTITION
  STORE BY ("@UNIQUE_EXECUTE_ID" ASC, "@UNIQUE_IUD_ID" ASC, A ASC)
  ;

--- SQL operation complete.
>>-- remember to remove the __TEMP suffix when the code is changed!!
>>
>>-- alter table in order to regenerate the temp table
>>alter table t232A add column e int;

--- SQL operation complete.
>>
>>update t232A set d=d+10;

--- 4 row(s) updated.
>>-- trigger should run fine on the modified table
>>
>>showddl TABLE ( TEMP_TABLE t232A__TEMP);

CREATE TABLE (TEMP_TABLE CAT1.SCHM.T232A__TEMP)
  (
    "@UNIQUE_EXECUTE_ID"             CHAR(SIZE) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT -- NOT NULL NOT DROPPABLE
  , "@UNIQUE_IUD_ID"                 INT NO DEFAULT -- NOT NULL NOT DROPPABLE
  , A                                INT NO DEFAULT -- NOT NULL NOT DROPPABLE
  , B                                INT NO DEFAULT -- NOT NULL NOT DROPPABLE
  , C                                INT NO DEFAULT -- NOT NULL NOT DROPPABLE
  , D                                INT DEFAULT NULL
  , E                                INT DEFAULT NULL
  , CONSTRAINT CAT1.SCHM.T232A__TEMP_748181761_2671 PRIMARY KEY
      ("@UNIQUE_EXECUTE_ID" ASC, "@UNIQUE_IUD_ID" ASC, A ASC) NOT DROPPABLE
  )
  LOCATION \NSK.$SYSTEM.ZSDZVD7K.BJVX7R00
  NAME NSK_SYSTEM_ZSDZVD7K_BJVX7R00
  RANGE PARTITION
  STORE BY ("@UNIQUE_EXECUTE_ID" ASC, "@UNIQUE_IUD_ID" ASC, A ASC)
  ;

--- SQL operation complete.
>>-- remember to remove the __TEMP suffix when the code is changed!!
>>log;