Statement level trigger to enforce a constraint

Question

I am trying to implement a statement level trigger to enforce the following "An applicant cannot apply for more than two positions in one day".

I am able to enforce it using a row level trigger (as shown below) but I have no clue how to do so using a statement level trigger when I can't use :NEW or :OLD.

I know there are alternatives to using a trigger but I am revising for my exam that would have a similar question so I would appreciate any help.

CREATE TABLE APPLIES(
anumber     NUMBER(6)   NOT NULL,  /* applicant number */
pnumber     NUMBER(8)   NOT NULL, /* position number */
appDate     DATE        NOT NULL, /* application date*/
CONSTRAINT APPLIES_pkey PRIMARY KEY(anumber, pnumber)
);

CREATE OR REPLACE TRIGGER app_trigger
BEFORE INSERT ON APPLIES
FOR EACH ROW
DECLARE 
  counter NUMBER;
BEGIN
  SELECT COUNT(*) INTO counter 
  FROM APPLIES 
  WHERE anumber = :NEW.anumber
  AND to_char(appDate, 'DD-MON-YYYY') = to_char(:NEW.appDate, 'DD-MON-YYYY');

  IF counter = 2 THEN
      RAISE_APPLICATION_ERROR(-20001, 'error msg');
  END IF;
END;

score 1 · Answer 1 · answered Jun 01 '19 at 17:56

1

Your rule involves more than one row at the same time. So you cannot use a FOR ROW LEVEL trigger: querying on APPLIES as you propose would hurl ORA-04091: table is mutating exception.

So, AFTER statement it is.

CREATE OR REPLACE TRIGGER app_trigger
AFTER INSERT OR UPDATE ON APPLIES
DECLARE 
  cursor c_cnt is
    SELECT 1 INTO counter 
    FROM APPLIES 
    group by anumber, trunc(appDate) having count(*) > 2;
  dummy number;
BEGIN
  open c_cnt;
  fetch c_cnt in dummy;
  if c_cnt%found then 
      close c_cnt;
      RAISE_APPLICATION_ERROR(-20001, 'error msg');
  end if;
  close c_cnt;
END;

Obviously, querying the whole table will be inefficient at scale. (One of the reasons why triggers are not recommended for this sort of thing). So this is a situation in which we might want to use a compound trigger (assuming we're on 11g or later).

answered Jun 01 '19 at 17:56

APC

144,005
19
170
281

1

One critical thing here .... it might not work. Because two sessions can both insert the same data and each will only see a count of 1 until they commit. Voila...now you have a data corruption. You need to lock all rows that might potentially be impacted here....that's a scalability killer. Take a look here https://asktom.oracle.com/pls/apex/asktom.search?tag=best-way-to-enforce-cross-row-constraints – Connor McDonald Jun 03 '19 at 11:56
@ConnorMcDonald - it is an unfortunate fact that almost every trigger-based homework assignment we see here falls into this trap. – APC Jun 03 '19 at 12:40
Amen to that :-) – Connor McDonald Jun 03 '19 at 14:04

score 1 · Accepted Answer · answered Jun 01 '19 at 19:12

You're correct that you don't have :OLD and :NEW values - so you need to check the entire table to see if the condition (let's not call it a "constraint", as that term has specific meaning in the sense of a relational database) has been violated:

CREATE OR REPLACE TRIGGER APPLIES_AIU
  AFTER INSERT OR UPDATE ON APPLIES
BEGIN
  FOR aRow IN (SELECT ANUMBER,
                      TRUNC(APPDATE) AS APPDATE,
                      COUNT(*) AS APPLICATION_COUNT
                 FROM APPLIES
                 GROUP BY ANUMBER, TRUNC(APPDATE)
                 HAVING COUNT(*) > 2)
  LOOP
    -- If we get to here it means we have at least one user who has applied
    -- for more than two jobs in a single day.

    RAISE_APPLICATION_ERROR(-20002, 'Applicant ' || aRow.ANUMBER ||
                                    ' applied for ' || aRow.APPLICATION_COUNT ||
                                    ' jobs on ' ||
                                    TO_CHAR(aRow.APPDATE, 'DD-MON-YYYY'));
  END LOOP;
END APPLIES_AIU;

It's a good idea to add an index to support this query so it will run efficiently:

CREATE INDEX APPLIES_BIU_INDEX
  ON APPLIES(ANUMBER, TRUNC(APPDATE));

dbfiddle here

Best of luck.

great example and explanantion! thanks for your well wishes too! — nic guo, Jun 02 '19 at 03:33

Statement level trigger to enforce a constraint

2 Answers2