CODEWITHSUNDEEP
sqloracle-databaseplsql
Brandon Tupiti
Brandon Tupiti

Reputation: 274

How to prevent insertion of data in oracle sql using triggers

I have a table called FACULTY which has a column named F_RANK and holds char data type. The rule is that the FACULTY table CANNOT have more than 2 professors with the rank F_RANK = 'Full'. Thus, if a new professor is inserted into the table with 'Full' OR one of the existing professors F_RANK gets updated to 'Full', it should fire a trigger that checks the total of professors with 'Full' and if it is 2 already, it should reject the insertion.

I started writing a trigger for it but stopped midway because I do not know how to handle it.

Here is my code.

create or replace TRIGGER TRG_ONLYTWOPROF
BEFORE INSERT OR UPDATE OF F_RANK ON FACULTY
DECLARE 
TOTAL NUMBER(4);
BEGIN
SELECT COUNT(F_RANK) INTO TOTAL 
FROM FACULTY
WHERE F_RANK = 'Full';
IF :NEW.F_RANK = 'Full' THEN
-- check if total is less than 2
   -- if it is, accept insertion
   -- else reject insertion of the row
ELSE
-- accept insertion of the row
-- or accept update of existing row on F_RANK column
END;
/

Can anyone help me out on this? Thanks in advance.

Upvotes: 1

Views: 871

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

You're really not too far off. You correctly saw that the trigger could not be a row trigger, as you'd need to query the table on which the trigger is defined. I suggest the following:

create or replace TRIGGER TRG_ONLYTWOPROF
  AFTER INSERT OR UPDATE OF F_RANK ON FACULTY
DECLARE 
  TOTAL NUMBER(4);
BEGIN
  SELECT COUNT(*)
    INTO TOTAL 
    FROM FACULTY
    WHERE F_RANK = 'Full';

  IF TOTAL > 2 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Failure in trigger TR_ONLYTWOPROF: two professors with RANK=''Full'' already exist');
  END IF;
END TRG_ONLYTWOPROF;

This defines a statement trigger which will fire after the INSERT or UPDATE has taken place. One problem is that a statement trigger can't see row values, so you can't skip the check if the new row has an F_RANK other than 'Full'. Shouldn't matter much but has to be kept in mind. The other is that because you can't see the row values, you need to perform the check after the statement has taken effect so that you can properly raise an exception.

Best of luck.

Upvotes: 1

Related Questions