Triggers based Q....ion [message #647703] |
Fri, 05 February 2016 19:47 |
|
M123
Messages: 47 Registered: February 2016 Location: USA
|
Member |
|
|
Hi all
I am trying to create a trigger on dept table.Dept table has following columns
JOB VARCHAR2(20 BYTE)
JOBID VARCHAR2(20 BYTE)
EMPID VARCHAR2(20 BYTE)
SALARY NUMBER(21,0)
i created trigger create or replace trigger new1 before
insert or update on dept
for each row
enable
declare
begin
If inserting then
insert into dept(job,jobid,empid,salary) values(:new.job,:new.jobid,:new.empid,:new.salary);
end if;
end;
trigger is compiled
and after that i used this statement, insert into dept values('analyst','31','7',3456);
but got an error:
Error starting at line : 16 in command -
insert into dept (JOB,JOBID,EMPID,salary) values('analyst','31','7',3456)
Error report -
SQL Error: ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "HR.NEW1", line 4
ORA-04088: error during execution of trigger 'HR.NEW1'
ORA-06512: at "HR.NEW1", line 4
ORA-04088: error during execution of trigger 'HR.NEW1'
ORA-06512: at "HR.NEW1", line 4
my intention is trigger should fire when i insert data into dept table
|
|
|
Re: Triggers based Q....ion [message #647704 is a reply to message #647703] |
Fri, 05 February 2016 20:32 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
You have a TRIGGER on INSERT against DEPT table
which contains insert into dept(job,jobid,empid,salary) values(:new.job,:new.jobid,:new.empid,:new.salary); that will fire the TRIGGER against DEPT
which contains insert into dept(job,jobid,empid,salary) values(:new.job,:new.jobid,:new.empid,:new.salary); that will fire the TRIGGER against DEPT
which contains insert into dept(job,jobid,empid,salary) values(:new.job,:new.jobid,:new.empid,:new.salary); that will fire the TRIGGER against DEPT
which contains insert into dept(job,jobid,empid,salary) values(:new.job,:new.jobid,:new.empid,:new.salary); that will fire the TRIGGER against DEPT
.
.
.
.
for a total of 50 times
Why do you ask what is the problem?
The TRIGGER code should never (or almost never) do SQL against the table upon which the trigger is based.
|
|
|
|
|
|
|
|
|
|
|
|
Re: Triggers based Q....ion [message #647741 is a reply to message #647709] |
Sat, 06 February 2016 15:50 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
M123 wrote on Fri, 05 February 2016 22:20[code]
i achieved the result when i used the program above, got a trigger fired when i inserted new values into dept table.
but one more question.
what is the use of the if inserting statement ,as mentioned in this url <redacted>
Danger, Will Robinson!
When I clicked on the link, I got several cpu-sucking pop-ups and a warning from McAfee that I had requested dangerous URL.
Though before everything went south I was able to get a look at the code and see that you severely mis-understood what it was doing vs. your own code. Looks like the trigger was on 'tablename' while the dml inside the trigger was on 'owner.tablename', which most likely is not the same table as 'tablename', which is understood to be owned by the owner of the trigger.
[Updated on: Sat, 06 February 2016 15:50] Report message to a moderator
|
|
|
|
|
|
|
Re: Triggers based Q....ion [message #647775 is a reply to message #647745] |
Sun, 07 February 2016 19:31 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
M123 wrote on Sat, 06 February 2016 19:58its ok i mentioned wrong trigger name , i created dept table and tried creating a trigger on it.i know i mentioned trigger name as "employee_salary_update" , this is on dept table only.
what is wrong with code apart from name ?
I said nothing about the trigger name. I was talking about the code within the trigger itself. The name is totally irrelevant.
Plus the fact that you wanted us to link to a risky site.
|
|
|