Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01438
ORA-01438 [message #208624] Mon, 11 December 2006 09:57 Go to next message
seyiisq
Messages: 125
Registered: September 2005
Senior Member
i created this trigger which worked fine

create or replace Trigger bad_reason_trig
After insert or update of decision on attendance_warning
For each row
Declare
i_state states.states_name%type;
begin
if
:new.absent = 2 and :new.decision = 'bad'
then
i_state := 'removed';
dbms_output.put_line ('STUDENT HAVE BEEN REMOVED FROM MODULE DUE TO BAD REASON');
end if;
insert into states values (state_seq.nextval,:new.student_id,i_state,:new.lecturer_id);
END bad_reason_trig;
/

table attendance_warning is as below
create table attendance_warning(
attendance_warning_id number(5),
student_id number(5),
lecturer_id number(5),
absent number(1),
reason varchar2(50),
decision varchar2(20),
constraint pk_student_lec_id primary key (attendance_warning_id),
constraint fk_aw_student_id foreign key (student_id) references student (student_id),
constraint fk_aw_lecturer_id foreign key (lecturer_id) references lecturer (lecturer_id));

table states is as below
create table states (
states_id number(1),
student_id number(1),
states_name varchar2(20),
constraint pk_states_id primary key (states_id),
constraint fk_s_sid foreign key (student_id) references student (student_id));

i got the error below after updating on attendance_warning table
SQL> update attendance_warning set decision = 'bad' where attendance_warning_id = 2;
STUDENT HAVE BEEN REMOVED FROM MODULE DUE TO BAD REASON
update attendance_warning set decision = 'bad' where attendance_warning_id = 2
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
ORA-06512: at "SCOTT.BAD_REASON_TRIG", line 10
ORA-04088: error during execution of trigger 'SCOTT.BAD_REASON_TRIG'
can u help pls

[Updated on: Mon, 11 December 2006 09:58]

Report message to a moderator

Re: ORA-01438 [message #208634 is a reply to message #208624] Mon, 11 December 2006 10:53 Go to previous message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You declared 'states.states_id' as NUMBER(1), and I believe that 'state_seq' sequence value reached 10.

Previous Topic: MeSSaGe from a Trigger
Next Topic: urgent:Error in trigger .
Goto Forum:
  


Current Time: Fri Dec 02 12:40:33 CST 2016

Total time taken to generate the page: 0.17429 seconds