|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
March 3rd, 2005, 10:27 AM
|
|||
|
|||
|
Trigger help!
the table:
Code:
create Table students ( student_no NUMBER(8) CONSTRAINT pk_student_no Primary Key, firstname Varchar2(15), lastname Varchar(20), streetno Varchar2(30), city varchar2(20), postcode varchar2(9), phone varchar2(15), userid char(5), enrolment_date date, enrolment_year number(4)) partition by range (enrolment_year) (partition S1 values less than (2001) tablespace comp3_5 Storage (initial 1K Next 1K), partition S2 values less than (2002) tablespace comp3_5 Storage (initial 1K Next 1K), trigger Code:
create or replace trigger calculateYearColumn
after insert on Students
declare
Y varchar2(4);
Y1 number(4);
begin
select to_char(to_date(enrolment_date, 'YYYY-MM-DD'), 'YYYY') into Y from students;
Y1:= to_number(Y);
update students set enrolment_year = Y1;
end;
/
insert statement Code:
Insert into students (student_no, firstname, lastname, streetno, city,postcode,phone,userid,enrolment_date)values(00022501,'S','A', '21 whatever','London','se118gf',02086545676,'as234','23-feb-2001') trigger is created fine, but after i insert valuesit gives me the error: Code:
ERROR at line 1: ORA-14402: updating partition key column would cause a partition change ORA-06512: at "AS234.CALCULATEYEARCOLUMN", line 7 ORA-04088: error during execution of trigger 'AS234.CALCULATEYEARCOLUMN' Last edited by paulh1983 : March 3rd, 2005 at 10:35 AM.
|
|
#2
March 3rd, 2005, 10:43 AM
|
||||
|
||||
|
Are you writing this trigger just to automatically add a value for enrolment_year when the only (user) inputed value is enrolment_date?
Are you aware that a view is enough? And that it does put less strain on database? And also, why did you partition the table just to put every partition on the same tablespace? More questions to you: Are you aware that you can partition this way (just an example): partition p_1 values less than to_char(to_date(enrolment_date, 'YYYY-MM-DD'), 'YYYY') tablespace tblspace? Are you aware that moving rows between partitions must be enabled? (I bet no on this one  )Code:
alter table tablename enable row movement;
__________________
My blog Tutorials about OSS databases, DBMonster ... Contribute to OSS Development, fill bug reports! Developer Shed eSupport Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins Random data (with a bias) Last edited by pabloj : March 3rd, 2005 at 10:49 AM.
|
|
#3
March 3rd, 2005, 11:21 AM
|
|||
|
|||
|
trigger yeah i want to get the year from the enrolment date!
thanks, lol! erm i dunno i am just following (or trying to) follow a tutorial given to us by our teacher! as for the tablespace, yeah you are right i am allowed access to TWO tablespaces and i should have put some partitions in one and some in other! actually i was a little bit too excited, it doesnt work! i get the trigger is mutating! is update and insert basically the same thing or different? Last edited by paulh1983 : March 3rd, 2005 at 02:35 PM.
|
|
#4
March 4th, 2005, 03:27 AM
|
||||
|
||||
|
For the mutate error read this
Insert adds a new row, update modifies an existing row or set of rows, you should really read some generic paper about relational databases if you have doubts of this kind. If you can change your insert statement you don't need the trigger at all, just etxract the year as a number from the user input and put it in the appropriate column. Code:
select to_number(to_char(to_date('1999-01-01', 'YYYY-MM-DD'), 'YYYY')) from dual
|
|
#5
March 6th, 2005, 10:18 AM
|
|||
|
|||
|
u know what it is! it worked for the first row ie when i inserted the very first row, after that i got the "returns more row than expected"
and i have also tried the first solution on that link u gave me, i put everything the same except for table name and the actual trigger but it still aint working Last edited by paulh1983 : March 6th, 2005 at 10:56 AM.
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > Trigger help! |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
