>>obey TEST_13_1(set_up);
>>INSERT INTO tab1A VALUES (1,2,3,4);

--- 1 row(s) inserted.
>>INSERT INTO tab1A VALUES (2,3,4,5);

--- 1 row(s) inserted.
>>INSERT INTO tab1A VALUES (3,4,5,6);

--- 1 row(s) inserted.
>>
>>---------------------------------
>>-- Define an index on column B
>>---------------------------------
>>CREATE INDEX i1 ON tab1a (b);

--- SQL operation complete.
>>SET parserflags 1;

--- SQL operation complete.
>> -- Allow select from index table.
>>
>>control query default TEMPORARY_TABLE_HASH_PARTITIONS $$TrigTempTablePartition1$$;

--- SQL operation complete.
>>obey TEST_13_1(tests);
>>------------------------------------------------------------------
>>--          TEST CASE
>>------------------------------------------------------------------
>>
>>-- Step 1: No triggers.
>>
>>UPDATE tab1a SET B=B+1;

--- 3 row(s) updated.
>>SELECT * FROM TAB1A;

A            B            C            D          
-----------  -----------  -----------  -----------

          1            3            3            4
          2            4            4            5
          3            5            5            6

--- 3 row(s) selected.
>>                  -- check result
>>SELECT * FROM TABLE(INDEX_TABLE i1);

B            A          
-----------  -----------

          3            1
          4            2
          5            3

--- 3 row(s) selected.
>>  -- check the index
>>DELETE FROM tab1a;

--- 3 row(s) deleted.
>>                    -- re-initialize
>>INSERT INTO tab1a VALUES (1,2,3,4), (2,3,4,5), (3,4,5,6);

--- 3 row(s) inserted.
>>
>>-- Step 2: An after trigger on a different column.
>>
>>CREATE TRIGGER atrig2
+>	AFTER UPDATE OF (c) ON tab1A
+>	REFERENCING OLD AS oldR, NEW AS newR
+>	FOR EACH ROW
+>	WHEN (newR.a < 3)
+>	INSERT INTO tab1B VALUES (newR.c, oldR.c);

--- SQL operation complete.
>>
>>UPDATE tab1a SET B=B+1;

--- 3 row(s) updated.
>>SELECT * FROM TAB1A;

A            B            C            D          
-----------  -----------  -----------  -----------

          1            3            3            4
          2            4            4            5
          3            5            5            6

--- 3 row(s) selected.
>>                  -- check result
>>SELECT * FROM TABLE(INDEX_TABLE i1);

B            A          
-----------  -----------

          3            1
          4            2
          5            3

--- 3 row(s) selected.
>>  -- check the index
>>SELECT * FROM TAB1b;

--- 0 row(s) selected.
>>DELETE FROM tab1a;

--- 3 row(s) deleted.
>>                    -- re-initialize
>>DELETE FROM tab1b;

--- 0 row(s) deleted.
>>INSERT INTO tab1a VALUES (1,2,3,4), (2,3,4,5), (3,4,5,6);

--- 3 row(s) inserted.
>>
>>-- Step 3: An after trigger on the same column.
>>
>>CREATE TRIGGER atrig3
+>	AFTER UPDATE OF (b) ON tab1A
+>	REFERENCING OLD AS oldR, NEW AS newR
+>	FOR EACH ROW
+>	WHEN (newR.a < 3)
+>	INSERT INTO tab1B VALUES (newR.b+10, oldR.b+10);

--- SQL operation complete.
>>
>>UPDATE tab1a SET B=B+1;

--- 3 row(s) updated.
>>SELECT * FROM TAB1A;

A            B            C            D          
-----------  -----------  -----------  -----------

          1            3            3            4
          2            4            4            5
          3            5            5            6

--- 3 row(s) selected.
>>                  -- check result
>>SELECT * FROM TABLE(INDEX_TABLE i1);

B            A          
-----------  -----------

          3            1
          4            2
          5            3

--- 3 row(s) selected.
>>  -- check the index
>>SELECT * FROM TAB1b;

A            B          
-----------  -----------

         13           12
         14           13

--- 2 row(s) selected.
>>DELETE FROM tab1a;

--- 3 row(s) deleted.
>>                    -- re-initialize
>>DELETE FROM tab1b;

--- 2 row(s) deleted.
>>DROP TRIGGER atrig2;

--- SQL operation complete.
>>DROP TRIGGER atrig3;

--- SQL operation complete.
>>INSERT INTO tab1a VALUES (1,2,3,4), (2,3,4,5), (3,4,5,6);

--- 3 row(s) inserted.
>>
>>-- Step 4: A before trigger on a different column.
>>
>>CREATE TRIGGER btrig4
+>	BEFORE UPDATE OF (c) ON tab1A
+>	REFERENCING NEW AS newR 
+>	FOR EACH ROW
+>	SET newR.c = newR.c + 1;

--- SQL operation complete.
>>
>>UPDATE tab1a SET B=B+1;

--- 3 row(s) updated.
>>SELECT * FROM TAB1A;

A            B            C            D          
-----------  -----------  -----------  -----------

          1            3            3            4
          2            4            4            5
          3            5            5            6

--- 3 row(s) selected.
>>                  -- check result
>>SELECT * FROM TABLE(INDEX_TABLE i1);

B            A          
-----------  -----------

          3            1
          4            2
          5            3

--- 3 row(s) selected.
>>  -- check the index
>>DELETE FROM tab1a;

--- 3 row(s) deleted.
>>                    -- re-initialize
>>INSERT INTO tab1a VALUES (1,2,3,4), (2,3,4,5), (3,4,5,6);

--- 3 row(s) inserted.
>>
>>-- Step 5: A before trigger on the same column.
>>
>>CREATE TRIGGER btrig5
+>	BEFORE UPDATE OF (b) ON tab1A
+>	REFERENCING NEW AS newR 
+>	FOR EACH ROW
+>	SET newR.b = newR.b + 1;

--- SQL operation complete.
>>
>>UPDATE tab1a SET B=B+1;

--- 3 row(s) updated.
>>SELECT * FROM TAB1A;

A            B            C            D          
-----------  -----------  -----------  -----------

          1            4            3            4
          2            5            4            5
          3            6            5            6

--- 3 row(s) selected.
>>                  -- check result
>>SELECT * FROM TABLE(INDEX_TABLE i1);

B            A          
-----------  -----------

          4            1
          5            2
          6            3

--- 3 row(s) selected.
>>  -- check the index
>>DELETE FROM tab1a;

--- 3 row(s) deleted.
>>                    -- re-initialize
>>DROP TRIGGER btrig4;

--- SQL operation complete.
>>DROP TRIGGER btrig5;

--- SQL operation complete.
>>INSERT INTO tab1a VALUES (1,2,3,4), (2,3,4,5), (3,4,5,6);

--- 3 row(s) inserted.
>>
>>-- Step 6: A before trigger promoting the indexed column
>>
>>CREATE TRIGGER btrig6
+>	BEFORE UPDATE OF (c) ON tab1A
+>	REFERENCING NEW AS newR 
+>	FOR EACH ROW
+>	SET newR.b = newR.b + 1;

--- SQL operation complete.
>>
>>UPDATE tab1a SET C=C+1;

--- 3 row(s) updated.
>>SELECT * FROM TAB1A;

A            B            C            D          
-----------  -----------  -----------  -----------

          1            3            4            4
          2            4            5            5
          3            5            6            6

--- 3 row(s) selected.
>>                  -- check result
>>SELECT * FROM TABLE(INDEX_TABLE i1);

B            A          
-----------  -----------

          3            1
          4            2
          5            3

--- 3 row(s) selected.
>>  -- check the index
>>DELETE FROM tab1a;

--- 3 row(s) deleted.
>>                    -- re-initialize
>>DROP TRIGGER btrig6;

--- SQL operation complete.
>>INSERT INTO tab1a VALUES (1,2,3,4), (2,3,4,5), (3,4,5,6);

--- 3 row(s) inserted.
>>
>>-- Step 7: An after trigger, IM not activated.
>>
>>CREATE TRIGGER atrig7
+>	AFTER UPDATE ON tab1A
+>	REFERENCING OLD AS oldR, NEW AS newR
+>	FOR EACH ROW
+>	WHEN (newR.a < 3)
+>	INSERT INTO tab1B VALUES (newR.c, oldR.c);

--- SQL operation complete.
>>
>>UPDATE tab1a SET C=C+1;

--- 3 row(s) updated.
>>SELECT * FROM TAB1A;

A            B            C            D          
-----------  -----------  -----------  -----------

          1            2            4            4
          2            3            5            5
          3            4            6            6

--- 3 row(s) selected.
>>                  -- check result
>>SELECT * FROM TABLE(INDEX_TABLE i1);

B            A          
-----------  -----------

          2            1
          3            2
          4            3

--- 3 row(s) selected.
>>  -- check the index
>>SELECT * FROM TAB1b;

A            B          
-----------  -----------

          4            3
          5            4

--- 2 row(s) selected.
>>DELETE FROM tab1a;

--- 3 row(s) deleted.
>>                    -- re-initialize
>>DELETE FROM tab1b;

--- 2 row(s) deleted.
>>DROP TRIGGER atrig7;

--- SQL operation complete.
>>INSERT INTO tab1a VALUES (1,2,3,4), (2,3,4,5), (3,4,5,6);

--- 3 row(s) inserted.
>>
>>-- Step 8: A before trigger, IM not activated.
>>
>>CREATE TRIGGER btrig8
+>	BEFORE UPDATE ON tab1A
+>	REFERENCING NEW AS newR 
+>	FOR EACH ROW
+>	SET newR.c = newR.c + 1;

--- SQL operation complete.
>>
>>UPDATE tab1a SET C=C+1;

--- 3 row(s) updated.
>>SELECT * FROM TAB1A;

A            B            C            D          
-----------  -----------  -----------  -----------

          1            2            5            4
          2            3            6            5
          3            4            7            6

--- 3 row(s) selected.
>>                  -- check result
>>SELECT * FROM TABLE(INDEX_TABLE i1);

B            A          
-----------  -----------

          2            1
          3            2
          4            3

--- 3 row(s) selected.
>>  -- check the index
>>DELETE FROM tab1a;

--- 3 row(s) deleted.
>>                    -- re-initialize
>>DROP TRIGGER btrig8;

--- SQL operation complete.
>>INSERT INTO tab1a VALUES (1,2,3,4), (2,3,4,5), (3,4,5,6);

--- 3 row(s) inserted.
>>
>>------------------------------------------------------------------
>>--           END
>>------------------------------------------------------------------
>>LOG;
