其他 - Oracle Apex 19.如何使用来自3个表的交互式报表创建表单？

一种选择是基于您编写的语句创建视图select。然后创建一个INSTEAD OF触发器，该触发器将注意发生在哪里。所有这些将在数据库内完成；Apex将仅用于显示值（IR）并将数据“传输”到视图（窗体），而使用触发器代替触发器将数据移动到适当的表中。

如何创建一个代替触发器？使用CREATE TRIGGER。例如，基于斯科特的模式：

这是一个视图：

SQL> create or replace view v_emp_dept as
  2  select d.deptno, d.dname, e.empno, e.ename, e.job, e.sal
  3  from emp e join dept d on e.deptno = d.deptno;

View created.

SQL> select * From v_emp_Dept;

    DEPTNO DNAME               EMPNO ENAME      JOB              SAL
---------- -------------- ---------- ---------- --------- ----------
        20 RESEARCH             7369 SMITH      CLERK            800
        30 SALES                7499 ALLEN      SALESMAN        1600
        30 SALES                7521 WARD       SALESMAN        1250
        20 RESEARCH             7566 JONES      MANAGER         2975
        30 SALES                7654 MARTIN     SALESMAN        1250
        30 SALES                7698 BLAKE      MANAGER         2850
        10 ACCOUNTING           7782 CLARK      MANAGER         2450
        20 RESEARCH             7788 SCOTT      ANALYST         3000
        10 ACCOUNTING           7839 KING       PRESIDENT       5000
        30 SALES                7844 TURNER     SALESMAN        1500
        20 RESEARCH             7876 ADAMS      CLERK           1100
        30 SALES                7900 JAMES      CLERK            950
        20 RESEARCH             7902 FORD       ANALYST         3000
        10 ACCOUNTING           7934 MILLER     CLERK           1300

14 rows selected.

SQL>

代替触发器可能看起来像这样：

SQL> create or replace trigger trg_ed
  2    instead of insert or update
  3    on v_emp_dept
  4    for each row
  5  begin
  6    if inserting then
  7       insert into dept (deptno, dname)
  8         values (:new.deptno, :new.dname);
  9       insert into emp (deptno, empno, ename, job, sal)
 10         values (:new.deptno, :new.empno, :new.ename, :new.job, :new.sal);
 11    elsif updating then
 12       update dept set
 13         dname = :new.dname
 14         where deptno = :new.deptno;
 15       update emp set
 16         deptno = :new.deptno,
 17         ename = :new.ename,
 18         job = :new.job,
 19         sal = :new.sal
 20         where empno = :new.empno;
 21    end if;
 22  end;
 23  /

Trigger created.

SQL>

测试：更新并插入：

SQL> update v_emp_dept set
  2    dname = 'accounting'
  3    where deptno = 10;

3 rows updated.

SQL> insert into v_emp_dept (deptno, dname, empno, ename, job, sal)
  2    values (99, 'New dept', 1, 'Littlefoot', 'Developer', 1000);

1 row created.

结果：

SQL> select * From dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        99 New dept                              --> new row
        10 accounting     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select * From emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
         1 Littlefoot Developer                           1000                    99 --> new row
      7369 SMITH      CLERK           7902 17.12.80        800                    20
      7499 ALLEN      SALESMAN        7698 20.02.81       1600        300         30
      7521 WARD       SALESMAN        7698 22.02.81       1250        500         30
      7566 JONES      MANAGER         7839 02.04.81       2975                    20
      7654 MARTIN     SALESMAN        7698 28.09.81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01.05.81       2850                    30
      7782 CLARK      MANAGER         7839 09.06.81       2450                    10
      7788 SCOTT      ANALYST         7566 09.12.82       3000                    20
      7839 KING       PRESIDENT            17.11.81       5000                    10
      7844 TURNER     SALESMAN        7698 08.09.81       1500          0         30
      7876 ADAMS      CLERK           7788 12.01.83       1100                    20
      7900 JAMES      CLERK           7698 03.12.81        950                    30
      7902 FORD       ANALYST         7566 03.12.81       3000                    20
      7934 MILLER     CLERK           7782 23.01.82       1300                    10

15 rows selected.

SQL>