OraFAQ Forum: SQL & PL/SQL » Birthday check

Home » SQL & PL/SQL » SQL & PL/SQL » Birthday check

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Birthday check [message #10125]

Tue, 06 January 2004 01:29

Furda
Messages: 1
Registered: January 2004

Junior Member

I want to check if the birthday is older then sysdate
I've done this like this :

constraint M_GEBDAT_CHECK
check(gebdatum > sysdate)

Everytime i run mu script Iracle complains that sysdate is not properly used .... :(

It may also be more complex (+ 18 or +21)
But like this its already good)

Already Thnx In avant ;)

Bye bye

Report message to a moderator

Re: Birthday check [message #10126 is a reply to message #10125]

Tue, 06 January 2004 01:44

Ken Jones
Messages: 70
Registered: January 2004

Member

Hi,

I think that sysdate cannot be used in a check constraint.

Ken

Report message to a moderator

Re: Birthday check [message #10127 is a reply to message #10126]

Tue, 06 January 2004 01:46

Furdah
Messages: 3
Registered: January 2004

Junior Member

Isn't there any other way how i can check this when I insert values

Thnx'y anyway ....
grtz

Report message to a moderator

Re: Birthday check [message #10128 is a reply to message #10125]

Tue, 06 January 2004 01:53

Ken Jones
Messages: 70
Registered: January 2004

Member

Use a simple before row insert trigger on each row before insertion into the table.

Have u used triggers before?

Ken

Report message to a moderator

Re: Birthday check [message #10129 is a reply to message #10128]

Tue, 06 January 2004 01:59

Furdah
Messages: 3
Registered: January 2004

Junior Member

Euh no , but we are not working with developer , we are designing the database in oracle and the further development are we going to do in VB.NET ,
BUT we have to catch everything in the database , So can we use triggers in oracle ???

Thx
Grtzzz

Report message to a moderator

Re: Birthday check [message #10136 is a reply to message #10129]

Tue, 06 January 2004 03:35

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

You don't need the Developer Suite. You can create a database trigger from SQL*Plus or a .sql script. Here is an example:

scott@ORA92> CREATE TABLE your_table_name
  2    (gebdatum DATE)
  3  /

Table created.

scott@ORA92> CREATE OR REPLACE TRIGGER m_gebdat_check
  2    BEFORE INSERT ON your_table_name
  3    FOR EACH ROW
  4  BEGIN
  5    IF :NEW.gebdatum <= SYSDATE
  6    THEN
  7  	 RAISE_APPLICATION_ERROR (-20001, 'Birthday must be greater than today');
  8    END IF;
  9  END m_gebdat_check;
 10  /

Trigger created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> INSERT INTO your_table_name (gebdatum) VALUES (SYSDATE + 1)
  2  /

1 row created.

scott@ORA92> INSERT INTO your_table_name (gebdatum) VALUES (SYSDATE - 1)
  2  /
INSERT INTO your_table_name (gebdatum) VALUES (SYSDATE - 1)
            *
ERROR at line 1:
ORA-20001: Birthday must be greater than today
ORA-06512: at "SCOTT.M_GEBDAT_CHECK", line 4
ORA-04088: error during execution of trigger 'SCOTT.M_GEBDAT_CHECK'

Report message to a moderator

Previous Topic:	stored procedure
Next Topic:	stored procedure in a constraint?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 20:40:05 CDT 2024