drop trigger imgr;
drop table emps;
drop table mgr;

CREATE TABLE emps (
eid    INTEGER  NOT NULL  GENERATED ALWAYS AS IDENTITY (START WITH  0 , INCREMENT BY  1 , NO CACHE ) PRIMARY KEY, 
salary INT NOT NULL);

CREATE TABLE mgr (eid  INTEGER  NOT NULL  GENERATED ALWAYS AS IDENTITY (START WITH  0 , INCREMENT BY  1 , NO CACHE ) PRIMARY KEY,
                  salary INT NOT NULL,
                  bonus  INT NOT NULL);

insert into emps (salary) values ( 10000 ),( 50000 );

CREATE TRIGGER iMgr 
 NO CASCADE BEFORE INSERT ON mgr 
 REFERENCING NEW AS n
 FOR EACH ROW
 SET n.bonus = coalesce(n.bonus, salary *  0 . 1 );
 
 WITH del AS (SELECT salary, eid
               FROM OLD TABLE(DELETE FROM emps WHERE eid =  1 )),
     ins AS (SELECT salary, old_salary, bonus, eid 
               FROM NEW TABLE (INSERT INTO 
                                mgr(salary)  
                                INCLUDE (old_salary INT) --включаем старую зарплату в выборку
                                SELECT salary *  1 . 05  ,
                                           salary --старая зарплата
                                  FROM del))
SELECT eid, old_salary, salary, bonus FROM ins;

--То есть в одном запросе ты получил данные из delete и из Insert.
--План очень эффективный ибо блокировок мало и они долго не держатся

EID         OLD_SALARY  SALARY      BONUS
----------- ----------- ----------- -----------
           0         10000         10500          1050 

  1  record(s) selected.

select * from emps;

EID         SALARY
----------- -----------
           0         50000 

   1  record(s) selected.

select * from mgr;

EID         SALARY      BONUS
----------- ----------- -----------
           0         10500          1050 

   1  record(s) selected.

Total Cost:  79 . 5758 

            TBSCAN 
              |
            SORT   
              |
            INSERT 
              |
            FILTER 
              |
            FILTER 
              |
            DELETE 
              |
            FETCH  
        .-----+-----.
      IXSCAN   TABLE: EMP
        |
  INDEX: EMP_PK