DB2 Old-New-Final-Table intermediate result Table

DB2 Old-New-Final-Table intermediate result TABLE DB2 bottom layer by maintaining the transaction TABLE, to add, update, and delete tables, these transaction tables are: NEW Table, old table, final table; they are also called intermediate result tables. When performing insert or update, the new table contains the data rows to be added or the data values to be updated. When performing update or delete, the old table contains the value to be updated or the value to be deleted. 1. The Old Table stores the updated or deleted data, similar to the data stored in the refferencing old as olddata in the trigger. The Old Table is only applicable to the update and delete operations. Create table empk (empno varchar (6), ename varchar (15), salary decimal (9, 2) insert into empk select empno, lastname, salary from employee order by salary desc fetch first 5 rows onlydb2 => select * from empk empno ename salary ------ --------------- ----------- 000010 HAAS 152750.00000030 KWAN 98250.00000070 PULASKI 96170.00000020 THOMPSON 94250.00000090 update empno to 89750.00 below HENDERSON 000090 employee Salary, when updating, I They want to see his old salary. Db2 => select * from old table (update empk set salary = 50000 where empno = '000000') empno ename salary ------ ------------- ----------- 000090 HENDERSON 000090 The following is the updated data: db2 => select * from empk where empno = '000000' empno ename salary ------ --------------------- 000090 HENDERSON 000090 when we delete data, check the deleted data: db2 => select * from old table (delete from empk where salary <80000) EMPNO ENAME SALA RY ------ --------------- ----------- 000090 HENDERSON 50000.00 2. New Tablenew table stores new data, similar to the New data values stored in the referrencing new as newdata trigger, and is only applicable to update and insert statements. While inserting data, we want to see the newly added values, but this can only view new data and cannot process the data as trigger does. Db2 => select * from new table (insert into empk values ('2013', 'yeexun ', 000050 )) empno ename salary ------ ------------- ----------- 000050 yeeXun 80000.00 the following example uses the three employees with a lower wage pair from the employee table and adds it to the empk table: select * from new table (insert into empk select empno, lastname, salary from employee order by salary asc fetch first 3 rows only) empno ename salary ------ ------------- ----------- 200340 ALONZO 31840.00000 290 PARKER 35340.00200330 WONG 35370.00 the data in this table is: db2 => select * from empk empno ename salary ------ ------------- ----------- 000010 HAAS 152750.00000030 KWAN 98250.00000070 PULASKI 96170.00000020 THOMPSON 94250.00000050 yeeXun 80000.00200340 ALONZO 31840.00000290 PARKER 35340.00200330 WONG 35370.00 raise the SALARY of employees with salaries lower than 800000, and check the salary after raise: db2 => select * from new table (update empk set salary = salary * 1.2 Where salary <80000) empno ename salary ------ ------------- ----------- 200340 ALONZO 38208.00000290 PARKER 42408.00200330 WONG 42444.00 3. Inlcude if we want to view the old data while updating a piece of data (Before update) based on the new table and old table mentioned above, we can use two statements to view the new data (after update), such: select salary from old table (update empk set salary = salary * 1.1 where empno = '20140901 ') union allselect salary from new table (update empk set salary = salary * 1.1 where empno = '2 00330 ') However, when executing this statement, we will get the following error message: SQL20165N in the context of the specified SQL data change statement, SQL data change statements in the FROM clause are not allowed. SQLSTATE = 428FL include keyword can solve this problem. When updating data, you can query the New and Old data at the same time. The following is an example: select empno, salary as new_salary, old_salary from new table (update empk include (old_salary decimal (1.1) set salary = salary * 200330, old_salary = salary where empno = '20140901 ') EMPNO NEW_SALARY OLD_SALARY ------ ----------- --------- 200330 46688.40 42444.00 1 record selected. The same is true for the data in the table: db2 => select * from empk where empno = '000000' empno ename salary ------ ------------- --------- 200330 WONG 200330 1 record selected. 4. Final Tablefinal table "Store" data modification operations, reference integrity operations, and data after trigger operations. It can be used to check when executing insert, update, or delete operations, whether the data to be operated has a trigger or reference constraint. The final table keyword causes the operation to stop. In the example below, we create a trigger to increase the salary of new employees by 10%. Create trigger trig_empkafter insert on empk referencing new as nfor each rowmode db2sqlupdate empk set salary = n. salary * 1.1 where empno = n. when executing the following statement, empno knows that final table can effectively avoid such hidden data changes. Select * from final table (insert into empk (empno, ename, salary) values ('20170101', 'chenlinbo', 120821 ))★SQL0989N AFTER trigger "TRIG_EMPK" tried to modify the rows modified by the SQL data change statement in the FROM clause in the table "EMPK. SQLSTATE = 560C3 db2 => select count (*) from empk where empno = '000000' 1 --------- 0 1 record selected. When adding data, use the new table to view the new value, which has not been modified by any trigger or constraint. Db2 => select * from new table (insert into empk values ('000000', 'chenlinbo', 120821) empno ename salary ------ ------------------- 50000 ChenLinBo 120821 --★1 record selected. Db2 => select * from empk where empno = '000000' empno ename salary ------ ------------- ----------- 120821 ChenLinBo 120821 --★1 record selected. 5. Differences between Final Table and New Table★They all return the same intermediate results of update or insert, but Final Table can ensure that the update and insert operations on the target Table do not have a trigger or subsequent data modifications that apply integrity constraints, as shown in the preceding example. Source http://blog.csdn.net/bobo12082119/article/details/8775600