What about a check constraint on a user column (you need to have a user
column):

create table testTable (
user CHAR(10) Check 15 > (select count(*) from testTable where
testTable.user = user)
)

This should only allow 15 records.

I don't know if this would work and how it would perform?

Herbert



|---------+----------------------------->
| | "Paul Schmidt" |
| | <paul@tricattechno|
| | logies.com> |
| | |
| | 27.03.2002 14:56 |
| | Please respond to |
| | ib-support |
| | |
|---------+----------------------------->

>--------------------------------------------------------------------------------------------------------------------------------------------------|

| |
| To: ib-support@yahoogroups.com |
| cc: |
| Subject: Re: [ib-support] limit the number of records? |

>--------------------------------------------------------------------------------------------------------------------------------------------------|




On 26 Mar 2002 at 16:06, Daniel Bertin wrote:

> Hi all,
> I'm a little puzzled,
> I would like to limit the number of records a user can enter into a
> table, and am looking for suggestions on how I should accomplish this.
> A routine on the client or on the server? on the client counting the
> number of records, how often or when should I count? on the server
> with a stored procedure, executed at certain intervals or on a
> trigger? I would like to stop the user from entering anymore records
> into a table after he has reached a certain limit(#of records). I'm
> using D5,IBO,FB1, Would someone have a suggestion on how I should
> accomplish this task? Thanks in advance Daniel
>

Some more info is needed, for example if user JBLOUGH is allowed to enter
75
records, is that a permanent limit, or can they add more records another
time?
What if he/she/it deletes 5 records, can they then add 5 more new ones?

The best way I know of, is to use a before insert trigger on the data entry
table, and
add a new table to track the entries. Optionally you can do the check
either within
the trigger (by raising an event on failure) or by having the client
program do a
comparison, before adding a record. By using a trigger you eliminate the
ability for
a client to get around the process by using multiple work stations. I
don't use events
myself, but I think that would result in cleaner code, and be a little
quicker. If a
delete will effectively increase the limit, then add a after delete trigger
that
decrements your counter.

PaulPaul Schmidt
Tricat Technologies
paul@...
www.tricattechnologies.com



To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/