Home » SQL & PL/SQL » SQL & PL/SQL » Count rows before inserting data.
Count rows before inserting data. [message #218866] Sun, 11 February 2007 13:32 Go to next message
rubbishmails11
Messages: 2
Registered: February 2007
Junior Member
if i have the following table
Customer(CustID, TelNo)
PK: TelNo
and i wish to restrict each customer to have a max of 3 TelNo.

Can someone guide or help me in writing the SQL statement to do the constraint?
Re: Count rows before inserting data. [message #218868 is a reply to message #218866] Sun, 11 February 2007 13:40 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
I'd write a BEFORE INSERT trigger
Re: Count rows before inserting data. [message #218869 is a reply to message #218866] Sun, 11 February 2007 14:00 Go to previous messageGo to next message
rubbishmails11
Messages: 2
Registered: February 2007
Junior Member
thanks for the tips, as i'm totally new on SQL, now i've problem constructing the trigger. So far i'm only able to understand up to this part.

create trigger maxPhone before insert on Customer
For each row
When((select count(custID) from Customer where custID='??')<3)
Begin
End;

1st Problem.
How do i tell oracle to count the custID which i'm entering?

2nd Problem.
Oracle is returning an error saying Subquery is not allowed. I guess its referring to Select statement in the when condition.

3rd Problem.
What should be done in the begin/end? I suppose i should be writing "insert into customer values('C123', 1234567);" but how do i make 'C123' and 1234567 a variable to refer to what i'm entering that triggers this code in the 1st place.
Re: Count rows before inserting data. [message #218870 is a reply to message #218866] Sun, 11 February 2007 14:12 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
The PL/SQL reference manual can be found at http://tahiti.oracle.com
Many fine coding examples can be found at http://asktom.oracle.com
Re: Count rows before inserting data. [message #218897 is a reply to message #218866] Sun, 11 February 2007 23:17 Go to previous message
tarmenel
Messages: 63
Registered: February 2007
Location: Israel
Member

You may want to look up the keyword HAVING in the link in from the above message. Its good to dig around a bit because you will actually learn something else in the process that may be helpful later on. The keyword is used after the where so try to read up on it a bit and if you don't come right just give me a shout.
Previous Topic: Unable to use sqlplus to unload CHAR(1) column with data = chr(0)
Next Topic: Manipulating triggers during insert
Goto Forum:
  


Current Time: Sun Dec 04 18:33:40 CST 2016

Total time taken to generate the page: 0.14055 seconds