RE: using User defined functions in Check constraint

[Mercadante, Thomas F]

Title: using User defined functions in Check constraint

Hussain

While you can use any Oracle supplied function within a check constraint, you cannot use a self-written function. 

But you can perform the same thing by using a database table trigger on the table itself.  You can basically validate anything you want from any schema you wish using a database trigger (with some exceptions).

 

A pre-insert trigger on the table would work just fine.  Check them out in the documentation.

 

hope this helps

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
From: Arslan Dar [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 19, 2001 1:14 PM
To: Multiple recipients of list ORACLE-L
Subject: using User defined functions in Check constraint

Hello All
Can we use user made functions in the check constraints?. Like In the check constraitns, we can use oracle functions, like LENGTH(NAME)=9. What we want to do is to check a value, which before being saved in the database(when we press save, registration no. of a patient, gets prefixed with the area code where the patient is being registrered), checks whether the values prefixed with the data are present in a table in another schema, for this purpose we want to use a function(selfmade). This we do to get a unique value irrespective of the area.

If not, is there a work around, to check such a value from a different schema&table in a check constraint.
Oracle 8.1.7 on winnt 4
TIA
Hussain
DBA SKMCH & RC