Home » SQL & PL/SQL » SQL & PL/SQL » Triggers based Q....ion (4.1.3 sql developer)
Triggers based Q....ion [message #647703] Fri, 05 February 2016 19:47 Go to next message
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 Confused
Re: Triggers based Q....ion [message #647704 is a reply to message #647703] Fri, 05 February 2016 20:32 Go to previous messageGo to next message
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 #647705 is a reply to message #647704] Fri, 05 February 2016 21:06 Go to previous messageGo to next message
M123
Messages: 47
Registered: February 2016
Location: USA
Member

http://tinypic.com/r/2wcl6pw/9

so i want to create a row level trigggers based on this and i want to use pseudocoolumn
Re: Triggers based Q....ion [message #647706 is a reply to message #647705] Fri, 05 February 2016 21:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
M123 wrote on Fri, 05 February 2016 19:06
http://tinypic.com/r/2wcl6pw/9

so i want to create a row level trigggers based on this and i want to use pseudocoolumn


is GOOGLE broken for you?

https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=oracle+row+level+trigger+example
Re: Triggers based Q....ion [message #647707 is a reply to message #647706] Fri, 05 February 2016 21:19 Go to previous messageGo to next message
M123
Messages: 47
Registered: February 2016
Location: USA
Member

ok let me check again ..
Re: Triggers based Q....ion [message #647708 is a reply to message #647707] Fri, 05 February 2016 21:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
TRIGGERS are coded to satisfy a business requirement.
Your original code make no sense.
Your code recursively INSERT same values into DEPT table.
You'd get the same result when no trigger code existed & no errors.
No errors would occur if you INSERT into different table.
Re: Triggers based Q....ion [message #647709 is a reply to message #647708] Fri, 05 February 2016 22:20 Go to previous messageGo to next message
M123
Messages: 47
Registered: February 2016
Location: USA
Member

CREATE OR REPLACE TRIGGER before_employee_salary_update
  BEFORE insert or UPDATE 
    ON dept
    FOR EACH ROW WHEN (new.empid>0)
   BEGIN
      dbms_output.put_line('id = ' || :old.empid);
      dbms_output.put_line('id = ' || :new.empid);
       dbms_output.put_line('Old salary = ' || :old.salary);
      dbms_output.put_line('New salary = ' || :new.salary);

 end;
 


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
http://tinypic.com/view.php?pic=2wcl6pw&s=9#.VrVy5Y-cHqk
Re: Triggers based Q....ion [message #647710 is a reply to message #647709] Fri, 05 February 2016 22:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>what is the use of the if inserting statement ,as mentioned in this url
Consider that TRIGGER can be used to maintain AUDIT table.
when INSERTING there are no :old values; only :new
when UPDATING there are both :old & :new values to be audited.
Re: Triggers based Q....ion [message #647711 is a reply to message #647710] Fri, 05 February 2016 23:13 Go to previous messageGo to next message
M123
Messages: 47
Registered: February 2016
Location: USA
Member

hello sir

my system suddenly restarted , i have to re-login in to session and again i disabled all the triggers on my temp table called dept
and ran the trigger ,

CREATE OR REPLACE TRIGGER ne11
  BEFORE  insert or UPDATE 
    ON dept
    FOR EACH ROW WHEN (new.empid>0)
   BEGIN
      dbms_output.put_line('id = ' || :old.empid);
      dbms_output.put_line('id = ' || :new.empid);
       dbms_output.put_line('Old salary = ' || :old.salary);
      dbms_output.put_line('New salary = ' || :new.salary);
 end;


insert into dept values('Quality','35','114',9001);

but this time trigger is not fired,only value is inserted i am little confused on this where i am doing wrong again ?
Re: Triggers based Q....ion [message #647718 is a reply to message #647711] Sat, 06 February 2016 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
this time trigger is not fired


How do you know that?
I suspect you just didn't execute "set serveroutput on".

Re: Triggers based Q....ion [message #647719 is a reply to message #647718] Sat, 06 February 2016 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also note that "new.empid>0" is quite stupid when you defined EMPID as a string.

Re: Triggers based Q....ion [message #647741 is a reply to message #647709] Sat, 06 February 2016 15:50 Go to previous messageGo to next message
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 #647745 is a reply to message #647741] Sat, 06 February 2016 19:58 Go to previous messageGo to next message
M123
Messages: 47
Registered: February 2016
Location: USA
Member

its 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 ?
Re: Triggers based Q....ion [message #647746 is a reply to message #647719] Sat, 06 February 2016 19:58 Go to previous messageGo to next message
M123
Messages: 47
Registered: February 2016
Location: USA
Member

yeh thats wrong , but i was just trying as a example Embarassed
Re: Triggers based Q....ion [message #647755 is a reply to message #647746] Sun, 07 February 2016 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you make such error in an example you will make it in real work, bad habits stay.
Declare number as number.
Use numbers and not strings for numbers.

Re: Triggers based Q....ion [message #647756 is a reply to message #647745] Sun, 07 February 2016 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
what is wrong with code apart from name ?


Michel Cadot wrote on Sat, 06 February 2016 08:06

Quote:
this time trigger is not fired


How do you know that?
I suspect you just didn't execute "set serveroutput on".

Re: Triggers based Q....ion [message #647775 is a reply to message #647745] Sun, 07 February 2016 19:31 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
M123 wrote on Sat, 06 February 2016 19:58
its 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.
Previous Topic: diffrence between stored procedure and package preocdures
Next Topic: Data Chucking based on Timewindow
Goto Forum:
  


Current Time: Fri Apr 26 03:32:16 CDT 2024