Home » SQL & PL/SQL » SQL & PL/SQL » Insert/Delete Trigger (Oracle 10g, Windows2000)
Insert/Delete Trigger [message #330699] Mon, 30 June 2008 21:02 Go to next message
rap.fernandes
Messages: 4
Registered: June 2008
Junior Member
Hi,

I need a small help on triggers,

I tried creating two triggers on EMP table, a row level insert and delete triggers, the insert trigger works but the delete trigger fails giving a mutating error. Can somebody explain me why this happens. Following is my code for reference,

CREATE OR REPLACE TRIGGER EMP_BEFORE_INS
BEFORE INSERT ON EMP FOR EACH ROW
DECLARE
v_count NUMBER(1);
BEGIN
SELECT COUNT(1) INTO v_count
FROM EMP
WHERE DEPTNO = :NEW.DEPTNO;

DBMS_OUTPUT.PUT_LINE('Employees in Dept : '||v_count);
END;
/

INSERT INTO EMP VALUES (8001, 'DAVID', 'ANALYST', 7566, '01-JAN-1998', 15000, NULL, 10);

INSERT INTO EMP VALUES (8002, 'GILES', 'DEVELOPER', 7566, '01-JAN-1998', 15000, NULL, 10);


CREATE OR REPLACE TRIGGER EMP_BEFORE_DEL
BEFORE DELETE ON EMP FOR EACH ROW
DECLARE
v_count NUMBER(1);
BEGIN
SELECT COUNT(1) INTO v_count
FROM EMP
WHERE DEPTNO = :OLD.DEPTNO;

DBMS_OUTPUT.PUT_LINE('Employees in Dept : '||v_count);
END;
/

DELETE EMP
WHERE DEPTNO = 10;

DELETE EMP
*
ERROR at line 1:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.EMP_BEFORE_DEL", line 4
ORA-04088: error during execution of trigger 'SCOTT.EMP_BEFORE_DEL'

Thanks

Raphael.
Re: Insert/Delete Trigger [message #330700 is a reply to message #330699] Mon, 30 June 2008 21:16 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
I am sorry to see that both the Search function on this forum & GOOGLE are broken for you.

Please be patient while repairs are underway.

More details will be available in the near future.
Re: Insert/Delete Trigger [message #330708 is a reply to message #330699] Mon, 30 June 2008 23:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

http://www.google.com/search?hl=en&q=ORA-04091&btnG=Google+Search

Regards
Michel
Re: Insert/Delete Trigger [message #330741 is a reply to message #330708] Tue, 01 July 2008 01:42 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

You can't Execute the Trigger since the table is in mutating state.

yo can do in the alternative by creating a temp table as :

CREATE OR REPLACE TRIGGER EMP_BEFORE_DEL
BEFORE DELETE ON EMP FOR EACH ROW
DECLARE
v_count NUMBER(1);
BEGIN
insert into temp values(:OLD.IB,:OLD.NAME,:OLD.DES,:OLD.DNO,:OLD.DOJ,:OLD.SAL,:OLD.FLAT_NO,:OLD.CNO);--WHERE DNO = :OLD.DNO;
select COUNT(1) INTO v_count FROM DUMP WHERE DNO = :OLD.DNO;
DBMS_OUTPUT.PUT_LINE('V_COUNT : ' || V_COUNT);
END;
/

Read the link for more info:

http://www.oracle-training.cc/t_easyoracle_pl_sql_triggers_and_mutating_tables.htm
Re: Insert/Delete Trigger [message #330748 is a reply to message #330741] Tue, 01 July 2008 02:00 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Your example only inserts the row that is deleted.
How can you ever count the number of records that are LEFT in that way?

The question to ask is why one would want to count these in a trigger. What is the business case for this?
If the reason is that (for example) dept must be deleted when there are no more emp's left, do this in a statement trigger, instead of a row trigger. (that is, if you want to do this in a trigger)

[Updated on: Tue, 01 July 2008 02:03]

Report message to a moderator

Previous Topic: triggers
Next Topic: convert from MM-DD-YYYY to DD-MM-YYYY
Goto Forum:
  


Current Time: Sun Dec 04 17:00:19 CST 2016

Total time taken to generate the page: 0.08822 seconds