|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
March 21st, 2005, 06:57 AM
|
|||
|
|||
|
Trigger Help! Compliation error
I have two tables, a client table with clientID, and a contact table with clientID as a foreign key. I wish to write a trigger that will not allow a user to input a new contact for a client where there are already 2 rows with the same client id. I have the following code:
Code:
create trigger contact_trigger Before insert on contact --for each row begin if (select count(contact.clientID) as contCount from contact where contact.clientid:=new.clientid)>=2 THEN raise_application_error(-20000, 'You''ve entered too many contacts') end if; end contact_trigger; (note: I have commented out the for each row statement) When I execute this code i get the following error Code:
Warning: Trigger created with compilation errors. Please can somebody direct me to: a) a method of debugging b) the actual error Thanks in advance
__________________
GermanJulian.com
|
|
#2
March 21st, 2005, 07:08 AM
|
||||
|
||||
|
Read an introductive tutorial
__________________
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)
|
|
#4
March 22nd, 2005, 03:36 AM
|
||||
|
||||
|
If you want your clientid to be unique simply add a unique constraint or set it as the primary key.
Note that your trigger does not check unicity in case of an update, is this what you want? And you didn't fetch the selected value into a variable.
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > Trigger Help! Compliation error |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
