Example:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> connect scott/[Email Address Removed] select count(1) from emp;
COUNT(1)
----------
14
SQL> create table emp_test as select * from emp;
Table created.
SQL> select count(1) from emp_test;
COUNT(1)
----------
14
SQL> create table deleted_emp (empno number, ename varchar2(40));
Table created.
SQL> create or replace trigger trap_delete_emp
2 after delete on emp_test
3 for each row
4 begin
5 insert into deleted_emp values(:old.empno, :old.ename);
6 end;
7 /
Trigger created.
SQL> select empno, ename from emp_test;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
EMPNO ENAME
---------- ----------
7900 JAMES
7902 FORD
7934 MILLER
14 rows selected.
SQL> delete from emp_test where empno=7369;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from deleted_emp;
EMPNO ENAME
---------- ---------------------
7369 SMITH
Now let's make it(the trigger) fire inside the procedure. And again It is
"not a direct call" to the trigger but
an event which make this fire..NOT the procedure but the event(which is the
DELETE)
SQL> create or replace procedure emp_delete_sp(p_empno in
emp_test.empno%type)
2 as
3 begin
4 execute immediate
5 'delete from emp_test where empno = :p_empno' using p_empno;
6
7 exception when others then raise;
8 end;
9 /
Procedure created.
SQL> exec emp_delete_sp(7788);
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select * from deleted_emp;
EMPNO ENAME
---------- ----------------------------------------
7369 SMITH
7788 SCOTT
All the Best,
Regis
-----Original Message-----
From: Shailesh Paliwal [mailto:[Email Address Removed] 29 August 2003 11:35
To: LazyDBA.com Discussion
Subject: Re: Can I Call a trigger from a Stored Procedure?
It will be great if u can explain it by an example.
*********************************************************************
This electronic transmission is strictly confidential and intended solely
for the addressee. It may contain information which is covered by legal,
professional or other privilege. If you are not the intended addressee,
you must not disclose, copy or take any action in reliance of this
transmission. If you have received this transmission in error,
please notify the sender as soon as possible.
This footnote also confirms that this message has been swept
for computer viruses.
**********************************************************************
Oracle LazyDBA home page