Thanks a lot boss, I understand what u said.

----- Original Message -----
From: "Regis Biassala" <Regis.[Email Address Removed] "'Shailesh Paliwal'" <[Email Address Removed] "LazyDBA.com Discussion"
<[Email Address Removed] Friday, August 29, 2003 4:34 PM
Subject: RE: Can I Call a trigger from a Stored Procedure?


> 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] Connected.
>
> SQL> 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] Sent: 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