Home » SQL & PL/SQL » SQL & PL/SQL » How to execute a procedure from trigger? (Oracle 10g R2)
How to execute a procedure from trigger? [message #378087] Sun, 28 December 2008 10:48 Go to next message
ssatyan.129
Messages: 4
Registered: August 2008
Location: Chennai
Junior Member
Dear All,
I have created a procedure which will add department_id & department_name into a table. Am calling this procedure in a trigger. Below I pasted the source code for both procedure & trigger.

PROCEDURE
----------
CREATE OR REPLACE PROCEDURE proc_add_dept
(
p_dept_id COPYDEPT.department_id%TYPE,
p_dept_name COPYDEPT.department_name%TYPE
)
IS
BEGIN
INSERT INTO COPYDEPT
( department_id, department_name )
VALUES
( p_dept_id, p_dept_name );
DBMS_OUTPUT.PUT_LINE('DEPT ID' || p_dept_id );
DBMS_OUTPUT.PUT_LINE('DEPT NAME' || p_dept_name );
END proc_add_dept;

TRIGGER
--------

CREATE OR REPLACE TRIGGER trig_after_dept_add
AFTER DELETE ON copydept FOR EACH ROW
BEGIN
proc_add_dept( 200,'Education' );
DBMS_OUTPUT.PUT_LINE('AFTER PROCEDURE CALL' );
END trig_after_dept_add;

I have written the code in such a way that whenever I try to delete a record from the table, a new record should insert into the table. But am getting the following error.
ORA-04091: table SATYAN.COPYDEPT is mutating, trigger/function may not see it
ORA-06512: at "SATYAN.PROC_ADD_DEPT", line 8
ORA-06512: at "SATYAN.TRIG_AFTER_DEPT_ADD", line 2
ORA-04088: error during execution of trigger 'SATYAN.TRIG_AFTER_DEPT_ADD'


Can anyone reply me What is the reason for the above error?

Thanks in advance.
Re: How to execute a procedure from trigger? [message #378088 is a reply to message #378087] Sun, 28 December 2008 11:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This error is one of the most asked, so search.

In addition, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: How to execute a procedure from trigger? [message #378092 is a reply to message #378087] Sun, 28 December 2008 13:20 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, if your goal is to keep the COPYDEPT table non-empty (such as that you can delete any number of records from it, but still have that one "200 - Education" department), error can be resolved by removing the FOR EACH ROW from the trigger creation script.

Here's an example; perhaps you'll find it helpful:
SQL> CREATE TABLE copydept
  2  (department_id NUMBER, department_name VARCHAR2(50));

Table created.

SQL> INSERT INTO copydept SELECT deptno, dname FROM DEPT;

4 rows created.

SQL> CREATE OR REPLACE TRIGGER trig_after_dept_add
  2  AFTER DELETE ON copydept
  3  BEGIN
  4    INSERT INTO copydept VALUES (200, 'Education');
  5  END;
  6  /

Trigger created.

SQL> DELETE FROM copydept WHERE department_id > 10;

3 rows deleted.

SQL> SELECT * FROM copydept;

DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------------------------------------
           10 ACCOUNTING
          200 Education

SQL>
Re: How to execute a procedure from trigger? [message #378261 is a reply to message #378087] Mon, 29 December 2008 09:29 Go to previous message
ssatyan.129
Messages: 4
Registered: August 2008
Location: Chennai
Junior Member
LittleFoot:
Thanks a lot for providing the solution.

Michal Cadot:
I will definitely format my posts in the future.

once again thanks a lot for your replies.
Previous Topic: will this code work properly
Next Topic: populate values when "not found" condition occurs
Goto Forum:
  


Current Time: Sun Dec 11 02:26:40 CST 2016

Total time taken to generate the page: 0.10167 seconds