Hello everybody nice to meet you !

Okay, let me explain.
I'm working under oracle and i don't understand why i have a fault in my table !!!

The problem is in the DateDeNaissance date constraint check_18 check((trunc(Sysdate,'YEAR')-trunc(DateDeNaissance,'YEAR')) >=18), ...

This check is here in order to verify that the Collaborateur is 18 ....
do someone can see where the fu*$%ing fault is ???


create table Collaborateur (
statut char(25) constraint check_statut check(statut='volontaire_benevole' or statut = 'membre_DAssociation'),
Grade char(25) not null,Fonction char(25) not null,
DateDeNaissance date constraint check_18 check((trunc(Sysdate,'YEAR')-trunc(DateDeNaissance,'YEAR')) >=18),
Matricule char(25) not null,
NomNom char(25) not null,
NomPrénom char(25) not null,
N° integer,
primary key (Matricule));


hmmm ... please help

oh yes ... sorry ...
here it is )

Error 2436
ORA-02436: date or system variable wrongly specified in CHECK constraint (DBD ERROR: error possibly near <*> indicator at char 254 in ' create table Collaborateur ( statut char(25) constraint check_statut check(statut='volontaire_benevole' or statut = 'membre_DAssociation'), Grade char(25) not null,Fonction char(25) not null, DateDeNaissance date constraint check_18 check((trunc(<*>Sysdate,'YEAR')-trunc(DateDeNaissance,'YEAR')) >=18), Matricule char(25) not null, NomNom char(25) not null, NomPrénom char(25) not null, N° integer, primary key (Matricule))')

From the Concepts manual, at URL http://download-west.oracle.com/docs/cd/B1...integ.htm#11424



The Check Condition

CHECK constraints enable you to enforce very specific integrity rules by specifying a check condition. The condition of a CHECK constraint has some limitations:

* It must be a Boolean expression evaluated using the values in the row being inserted or updated, and
* It cannot contain subqueries; sequences; the SQL functi
* SYSDATE, UID, USER, or USERENV; or the pseudocolumns LEVEL or ROWNUM.



Note the last condition.

-Mark

ow .... and then how can i do ? ((

As far as I know, only one way left . Use a trigger.
After insert/update trigger for each row. Use the same validation against :new.datedenaisance. And raise an error if the collaborateur is over 18.

Regards,

Arian

pfff ...i'm just so bad !!!
now i've made my beautiful trigger :

CREATE TRIGGER Maj_18
AFTER UPDATE OR INSERT
ON Collaborateur
FOR EACH ROW
BEGIN
IF trunc(Sysdate,'YEAR')-trunc(:new.DateDeNaissance,'YEAR')) < 18 THEN
RAISE_APPLICATION_ERROR(-20005,'Le collaborateur doit avoir 18 ans REVOLUS');
END IF;
END;

but it writes something like :



Strange no ???

and moreover i got a worst problem !!!

when i do :
trunc(Sysdate,'YEAR')-trunc(:new.DateDeNaissance,'YEAR')) < 18
thanks to the oracle treatment of date i can't have the good result because if i write as DateDeNaissance : '01 Mar 1977' it just keeps 77 and not 1977 ... so it leads to a bug ... because ... well you can understand why !


hmmm ....
well sorry i'm a Oracle newbie )

You missed a parenthesis. Add one after the 'IF' and before the first "trunc",
and your code should compile. But, yes, you're right, it's flawed.

I would replace:

"IF (trunc(Sysdate,'YEAR')-trunc(:new.DateDeNaissance,'YEAR')) < 18 THEN"

with something like:

"IF (sysdate-:new.DateDeNaissance)/365.2425 < 18 THEN"

if the DateDeNaissance field contains hours, minutes, seconds. If doesn't,
add a trunc() around sysdate, like this:

"IF (trunc(sysdate)-:new.DateDeNaissance)/365.2425 < 18 THEN"

Hope that helps,

-Mark

Hey thanks for the parenthesis .... but ... it still don't compile .... hrmmmm ...
okay .... i don't wanna bother you .... i'm sorry


CREATE TRIGGER Maj_18
AFTER UPDATE OR INSERT
ON Collaborateur
FOR EACH ROW
BEGIN
IF ((trunc(sysdate)-:new.DateDeNaissance)/365.2425 < 18) THEN
RAISE_APPLICATION_ERROR(-20005,'Le collaborateur doit avoir 18 ans REVOLUS');
END IF;
END;




moreover i'll add the Collaborateur table :



.... pffff sorry for that guys

Just a guess

Create or replace?

well ... create

OKAY ... it works ... :



and guess what ? now i have a new one ...
well i've done that :



and it tells me that :


okay i understand the matter ... but how can i replace :new.Date_JJMMAAAA ???

i don't understand ... in fact i didn't knew that it could have problem because of that ... grmbmlbl

Hi,

add "for each row", to make it a row-level trigger.

Regards,

Arian

Okay thanks to all of us i got something like :



it said me something like ...



in fact i got a table which name is ConvoiMedical who got a reference to Convoi ...
how can i go throught that reference ???

and how can i create this ConvoiMedical then ???
(i mean how to insert a new one with the Convoi attributes ???)




thanks