CODEWITHSUNDEEP
oracle-databaseplsqltriggers
maks
maks

Reputation: 6006

Before insert trigger

I want to disable in before insert trigger inserting into table when some condition is true

 create or replace trigger CHECK_FOR_MAX_ENTRANTS
before insert on application
declare
    entrants_count number;
    max_entrants number;
begin
    select count(*) into entrants_count from application 
    where id_speciality = :new.id_speciality;

    select max_students_number into max_entrants from speciality s
    where s.id_speciality = :new.id_speciality;

    IF entrants_count >= max_entrants THEN
        **disable this insert**
end;

How can i do this?

Upvotes: 0

Views: 4873

Answers (2)

Gerrat
Gerrat

Reputation: 29730

Assuming you're talking about Oracle, then, in place of disable this insert you could:

IF entrants_count >= max_entrants THEN
    raise_application_error(-21010, 'Max number of Entrants Reached');
END IF;

See: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm#sthref2006

EDIT: It's generally a bad idea to have inserts fail silently (what you're asking for). You also may run into mutating table errors if you try to delete the record in an after insert trigger.

Instead, just don't insert the record to begin with. One way you could probably achieve this is to add something like this to the end of your insert statement:

WHERE EXISTS SELECT null FROM 
(SELECT COUNT(*) entrants_count FROM application 
WHERE id_speciality = :new.id_speciality) a,
(SELECT max_students_number max_entrants 
FROM speciality WHERE id_speciality = :new.id_speciality) s
WHERE a.entrants_count < s.max_entrants

This should only execute the insert statement when entrants_count < max_entrants (what you want).

Upvotes: 3

tbone
tbone

Reputation: 15493

Try raising an error:

IF entrants_count >= max_entrants THEN
  raise_application_error(-20001, 'Cannot perform this insert!');
END IF;

Upvotes: 2

Related Questions