Home » SQL & PL/SQL » SQL & PL/SQL » Triggers -Help
Triggers -Help [message #248028] Wed, 27 June 2007 17:15 Go to next message
swapnajojo
Messages: 40
Registered: June 2007
Location: India
Member
What goes wrong here ?How can i corrct this trigger so as to work prperly
i have tried with BEFORE option also o




SQL> get mutetrig
1 CREATE OR REPLACE TRIGGER mteeg
2 AFTER update on emp
3 --for each row
4 begin
5 update emp set ename ='DIYA' where empno =6666;
6 EXCEPTION
7 WHEN OTHERS THEN
8 RAISE_APPLICATION_ERROR(-20666,'Out of memory');
9* end;
SQL> @mutetrig
10 /

Trigger created.

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
6666 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT Maker 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
2222 ADAMS CLERK 7788 23-MAY-87 1100 20
3330 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD Watching 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> update emp set job ='Dance' where ename ='MILLER'
2 /
update emp set job ='Dance' where ename ='MILLER'
*
ERROR at line 1:
ORA-20666: Out of memory
ORA-06512: at "SCOTT.MTEEG", line 5
ORA-04088: error during execution of trigger 'SCOTT.MTEEG'


SQL> spool off
Re: Triggers -Help [message #248038 is a reply to message #248028] Wed, 27 June 2007 17:54 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
whe you try to update empno 6666 thenb what it do? the same error.


ORA-04088:	error during execution of trigger 'string.string'
Cause:	A runtime error occurred during execution of a trigger.
Action:	Check the triggers which were involved in the operation.
Re: Triggers -Help [message #248090 is a reply to message #248028] Thu, 28 June 2007 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Laughing
You update that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp that fires a trigger that updates emp.

Did I say that the trigger fires?

Regards
Michel
Re: Triggers -Help [message #248094 is a reply to message #248028] Thu, 28 June 2007 01:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
7 WHEN OTHERS THEN
8 RAISE_APPLICATION_ERROR(-20666,'Out of memory');


What's this stupid 'translation' for? You change each and every possible error into an Out of memory error..
Get rid of the entire 'exception handler'
Re: Triggers -Help [message #248099 is a reply to message #248094] Thu, 28 June 2007 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I kept it for my next answer when he'll ask why this is THIS error that comes. Wink

Regards
Michel
Re: Triggers -Help [message #248100 is a reply to message #248094] Thu, 28 June 2007 01:21 Go to previous message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

What you are actually trying to do? Can you please elaborate.
Previous Topic: CRITICAL SELECT STATEMENT
Next Topic: find max val in rows?
Goto Forum:
  


Current Time: Mon Dec 05 13:20:21 CST 2016

Total time taken to generate the page: 0.38453 seconds