| ORA-04088: error during execution of trigger [message #46975] |
Wed, 14 July 2004 00:01  |
kris
Messages: 43
Registered: February 2002
|
Member |
|
|
Hi All,
I had written a before update DB trigger on emp table. Whenever I update the salary the changes should be inserted in history tables. I also have a condition that whenver the sal < 500 or > 7000 then the salary column in emp table should not be updated but the values should get inserted in history table. I have written this code but it is giving me error..
Can anyone tell where iam going wrong
SQL> create or replace trigger tsal before update of sal on emp
2 for each row
3 Begin
4 insert into history values (:old.empno, :old.sal, :new.sal);
5 if (:new.sal < 500) or (:new.sal > 5000) then
6 raise_application_error(-20101,'Salary not in range');
7 end if;
8
9 end;
10 /
Trigger created.
SQL> show errors
No errors.
SQL> update emp set sal=300 where empno=7369;
update emp set sal=300 where empno=7369
*
ERROR at line 1:
ORA-20101: Salary not in range
ORA-06512: at "SCOTT.TSAL", line 4
ORA-04088: error during execution of trigger 'SCOTT.TSAL'
|
|
|
|
| Re: ORA-04088: error during execution of trigger [message #46976 is a reply to message #46975] |
Wed, 14 July 2004 01:11  |
Jai Vrat Singh
Messages: 204
Registered: September 2002
Location: Singapore
|
Senior Member |
|
|
Whatever DML you isuue in a trigger is the part of main transaction( I mean the transaction that you re actually doing outside the trigger.
Wherever the error/or raise application error occurs ( in trigger or in main transaction), whole transaction gets affected.
However, you can detach trigger's transaction and make insert in the history table work you should make it an autonomous transaction.
This is file aut.sql
____________________________________________________
drop table history;
create table history(empno NUMBER(4), sal NUMBER(7,2), new_sal number(7,2));
create or replace trigger tsal before update of sal on emp
for each row
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
insert into history values (:old.empno, :old.sal, :new.sal);
if (:new.sal < 500) or (:new.sal > 7000) then
:new.sal := :old.sal;
end if;
commit;
end;
/
____________________________________________________
WHEN I RUN THIS
SQL> select * from history;
no rows selected
SQL> select * from emp where empno = 7369;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
SQL> @aut
Table dropped.
Table created.
Trigger created.
SQL> select * from history;
no rows selected
SQL> begin
2 update emp set sal=300 where empno=7369;
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select * from history;
EMPNO SAL NEW_SAL
---------- ---------- ----------
7369 800 300
SQL> select * from emp where empno = 7369;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
SQL> begin
2 update emp set sal= 501 where empno = 7369;
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select * from emp where empno = 7369;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 501 20
SQL> select * from history;
EMPNO SAL NEW_SAL
---------- ---------- ----------
7369 800 300
7369 800 501
SQL>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
