PL/SQL

[gazflat33]

Hi, I would like some help on creating a procedure to call a trigger.

I would like to not allow inserts of students onto attendances of offerings that would give a count above a maximum.

So say an offering has a cap of 20 students, i want to be able to stop any inserts if the limit is reached.

 

[avjoshi]

You may want to include that functionality in your front end program.

Anand.

 

[sem]

Trigers can not be called, they're invoked by events. You may count rows in post-insert trigger.

Regards, Dima

http://seminihin.narod.ru

 

[gazflat33]

"You may count rows in post-insert trigger"

Hi sem, could you give me an example please?

 

[SantaMufasa]

Gaz,

Within any PL/SQL block (trigger or not), you may say:

SELECT COUNT(*) into <variable> FROM <table_name>
WHERE <some_condition>;

Your CLASS_REGISTRATION table could have a &quot;STATUS&quot; column with status codes that represent statuses that include:
&quot;Confirmed&quot;
&quot;Wait List&quot;
&quot;No Hope&quot;
et cetera

Additionally, your parent table, something like &quot;CLASS_SESSION_INFO&quot; should have columns &quot;CAP&quot; and &quot;MAX_WAIT_LIST&quot;, which you will want to &quot;SELECT...INTO&quot; PL/SQL variables, as well.

Your SELECT statement might look something like:

SELECT COUNT(*) INTO CONFIRMED_HOLD
FROM CLASS_REGISTRATION
WHERE CLASS_REGISTRATION.STATUS = 'Wait List'
AND CLASS_REGISTRATION.SESSION_ID = CLASS_SESSION_INFO.ID;

If the COUNT is less than CLASS_SESSION_INFO.CAP, then set CLASS_REGISTRATION.STATUS = 'Confirmed'; if COUNT is greater than CLASS_SESSION_INFO.CAP, then

SELECT COUNT(*) INTO WAIT_LIST_HOLD
FROM CLASS_REGISTRATION
WHERE CLASS_REGISTRATION.STATUS = 'Wait List'
AND CLASS_REGISTRATION.SESSION_ID = CLASS_SESSION_INFO.ID;

If WAIT_LIST_HOLD < MAX_WAIT_LIST_HOLD, then set their CLASS_REGISTRATION.STATUS = 'Wait List', otherwise set their CLASS_REGISTRATION.STATUS = 'No Hope';

Obviously, some of the above code is pseudo-code; the SELECT statements are syntactically correct given matching table and PL/SQL variable definitions.

Let us know if this gives you a vision of how to build your trigger.

Mufasa
(aka Dave of Sandy, Utah, USA @ 11:40 (03Dec03) GMT, 04:40 (03Dec03) Mountain Time)

 

[sem]

One more note: you should also lock the whole table for each insert, because your statement may count only commited data and data changed within current transaction. So is another session adds more rows, the final number of rows may exceede the limit.
As an option you may lock 1 row in separate table created to hold current record count.
Can you explain why you need to limit the number of rows?

Regards, Dima

http://seminihin.narod.ru