Home » SQL & PL/SQL » SQL & PL/SQL » Abt Trigger
Abt Trigger [message #196410] Thu, 05 October 2006 04:13 Go to next message
royal
Messages: 4
Registered: October 2006
Location: Singapore
Junior Member
Hi Smile

I having problem update the field that i want Embarassed and i cant find the mistake i made

My work - insert a new student record and check student from company with training budget less than 350k are to place on the passpost program field to 'Y'

CREATE TABLE company (
company_id number(2),
company_name varchar2(15),
city varchar2(12),
industry varchar2(15),
no_employees number(5),
revenue number(10),
training_budget number(6),
CONSTRAINT pk_company PRIMARY KEY (company_id)
);

CREATE TABLE student (
student_id number(4),
student_fname varchar2(10),
student_lname varchar2(12),
tel_no varchar2(15),
fax_no number(15),
gender varchar2(1),
date_of_birth date,
student_desc varchar2(25),
preferred_language varchar2(2),
passport_program varchar2(1),
company_id number(2) not null,
CONSTRAINT pk_student PRIMARY KEY (student_id),
CONSTRAINT fk_company
FOREIGN KEY (company_id)
REFERENCES company (company_id)
);


http://www.geocities.com/blue_liang79/T1.jpg

http://www.geocities.com/blue_liang79/T2.jpg

Thanks
Re: Abt Trigger [message #196412 is a reply to message #196410] Thu, 05 October 2006 04:18 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Could it be:

s.company_id in (select passport_program ...

since company_id is a number and passport_program is a varchar2.
Re: Abt Trigger [message #196414 is a reply to message #196410] Thu, 05 October 2006 04:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) Could you post actual code in CODE tags rather than images. That way we can cut and paste the code out to test it.

2) DON'T do things like this as System. System is special. Use a normal user account.

3) I suspect the problem is the line in the After trigger that says
s.company_id in (select s.passport_program....


Passport_program is Varchar2(1), Company_id is a Number(2)
The optimiser will be expecting a numeric value from the subquery, and will be objecting when it comes across the ocasional 'Y' in the passport_program column.

Damn! Beaten to it.

[Updated on: Thu, 05 October 2006 04:24]

Report message to a moderator

Re: Abt Trigger [message #196529 is a reply to message #196410] Thu, 05 October 2006 17:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
In addition to the problems that are causing your current errors, your approach is all wrong. You should avoid selecting from the same table that a trigger is on, because you will ultimately end up with a mutating error, once the other errors have been resolved. You should select from the company table into a variable within the trigger code. Then use a comparison of that value to 350000 to determine whether to set the :new value of passport_program, without issuing an update statement. This should all be done in one row trigger, no statement triggers.
Re: Abt Trigger [message #196560 is a reply to message #196410] Thu, 05 October 2006 22:40 Go to previous messageGo to next message
royal
Messages: 4
Registered: October 2006
Location: Singapore
Junior Member
I have solved it by changing

where s.company_id in (select s.company_id from

It work Smile

Thx to Cthulhu and JRowbottom


Barbara, I understand what u going to tell me and i will try to do it. Smile
Re: Abt Trigger [message #196828 is a reply to message #196410] Sun, 08 October 2006 02:06 Go to previous messageGo to next message
royal
Messages: 4
Registered: October 2006
Location: Singapore
Junior Member
Sad I have tried to run the trigger again and it's giving me a new error....why can run at the 1st time and now cant?

Can anyone tell me where the mistake i made?

ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "SYSTEM.AFTER_STU_INSERT", line 4
ORA-04088: error during execution of trigger 'SYSTEM.AFTER_STU_INSERT'
ORA-06512: at "SYSTEM.AFTER_STU_INSERT", line 8

===============================================
create or replace package pck_course is
pck_company_id Number;
pck_budget Number;
pck_student_id Number;
end pck_course;
/



create trigger before_Stu_Insert
before insert or update on student
for each row
begin
pck_course.pck_company_id := :new.company_id;
pck_course.pck_student_id := :new.student_id;
end;
/


create trigger after_stu_insert
after insert or update on student
declare
budget company.training_budget%type;
begin
select training_budget into budget
from company
where company_id = pck_course.pck_company_id;
if (budget < 350000) then
update student set passport_program = 'Y'
where student_id = pck_course.pck_student_id
and company_id = pck_course.pck_company_id;
end if;
end;
/
  • Attachment: courses.sql
    (Size: 4.01KB, Downloaded 97 times)

[Updated on: Mon, 09 October 2006 01:23]

Report message to a moderator

Re: Abt Trigger [message #197276 is a reply to message #196828] Tue, 10 October 2006 20:02 Go to previous message
royal
Messages: 4
Registered: October 2006
Location: Singapore
Junior Member
Just wanna tell i have solved the problem.

I was getting the info from this url posted by rikfair in post topic "Trigger mutation error " http://www.astral-consultancy.co.uk/cgi-bin/hunbug/doco.cgi?11010

Thx rikfair Smile It's really a good way to solve mutating error ^^

[Updated on: Tue, 10 October 2006 20:03]

Report message to a moderator

Previous Topic: Decode problem
Next Topic: SQL Select - Data from one row output to separate rows
Goto Forum:
  


Current Time: Sat Dec 10 14:54:47 CST 2016

Total time taken to generate the page: 0.22661 seconds