如
1.
create or replace trigger TRI_ROW_TEST
AFTER INSERT OR UPDATE OR DELETE
ON CNBKG1047
FOR EACH ROW
declare
begin
if (inserting) then
f_insert_itf(); --关键是这里,怎么把:new 作为参数传递给f_insert_itf
end;2.
create or replace function f_insert_itf(p_row in itf%rowtype)
is
begin
..............
end;
1.
create or replace trigger TRI_ROW_TEST
AFTER INSERT OR UPDATE OR DELETE
ON CNBKG1047
FOR EACH ROW
declare
begin
if (inserting) then
f_insert_itf(); --关键是这里,怎么把:new 作为参数传递给f_insert_itf
end;2.
create or replace function f_insert_itf(p_row in itf%rowtype)
is
begin
..............
end;
我试过如下
p_row itf%ROWTYPE;
p_row := :new
出错~~
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(20)
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y
SQL> desc emp_bak
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4) Y
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y
SQL> create or replace function f_insert_itf(p_row in emp%rowtype)return number
2 is
3 v_num number(10);
4
5 begin
6 v_num:=1;
7 return v_num;
8 end;
9 /
Function created
SQL>
SQL> CREATE OR REPLACE TRIGGER TRI_ROW_TEST
2 AFTER INSERT
3 ON emp_bak
4 FOR EACH ROW
5 declare
6 v_row emp%rowtype;
7 v_num number(10);
8 begin
9 if inserting then
10 select
11 :new.EMPNO ,
12 :new.ENAME ,
13 :new.JOB ,
14 :new.MGR ,
15 :new.HIREDATE ,
16 :new.SAL ,
17 :new.COMM ,
18 :new.DEPTNO
19 into
20 v_row.EMPNO ,
21 v_row.ENAME ,
22 v_row.JOB ,
23 v_row.MGR ,
24 v_row.HIREDATE ,
25 v_row.SAL ,
26 v_row.COMM ,
27 v_row.DEPTNO
28 from dual;
29 v_num:=f_insert_itf(v_row);
30 end if;
31 dbms_output.put_line(v_num);
32 end;
33 /
Trigger created
SQL> set serveroutput on
SQL> insert into emp_bak(empno)values(10);
1
1 row inserted
SQL>
没有这种写法直接把:new.col 传给函数中的表类型的参数 只能间接来
只能先定义一个%rowTYPE的参数,然后再将:new的每个值依次赋值吗?