CODEWITHSUNDEEP
oracle-databaseplsqltriggers
j.hall
j.hall

Reputation: 61

How to count before new insertion using the values from the insert statement

I am trying to create a trigger in which it will count the number of a occurrence and deny any new insertion if it hits more than a number.

In my case, if I attempt to add in a new testing with the testerid which is 1001 (testing table already have 3 records with testerid of 1001), it should denied the insertion.

create table tester
(
    testerid int,
    CONSTRAINT testerpk PRIMARY KEY (testerid)
);

create table testing
(
    testingid int,
    testingdate date,
    testerid int,
    CONSTRAINT testerfk FOREIGN KEY (testerid) REFERENCES tester(testerid)
);

insert into tester
values (1001);
insert into tester
values (1002);
insert into testing
values (1, (Date '2020-10-10'), 1001);
insert into testing
values (2, (Date '2020-10-11'), 1001);
insert into testing
values (3, (Date '2020-10-12'), 1001);

CREATE OR REPLACE TRIGGER trg
BEFORE INSERT
ON testing
FOR EACH ROW

DECLARE
    val number;

BEGIN
    SELECT COUNT(*)
    INTO val
    FROM testing
    WHERE :new.testerid = testerid
    HAVING val < 3;
END;

insert into testing
values (10, (Date '2020-11-17'), 1001);

How do I write in my trigger the check for the inputted testerid? I tried WHERE :new.testerid = testerid but I think that is wrong, as I could not tell if the testerid is indeed stemming from the input statement .

Here is my dbfiddle (it is throwing errors)

Upvotes: 0

Views: 346

Answers (2)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65323

Get rid of HAVING Clause ( even GROUP BY expression is not needed ). Use below code without raising any exception with RAISE_APPLICATION_ERROR added in order to check out whether the val exceeds three :

CREATE OR REPLACE TRIGGER trg
BEFORE INSERT ON testing
FOR EACH ROW
DECLARE
  val INT;
BEGIN
  SELECT COUNT(*) 
    INTO val 
    FROM testing 
   WHERE testerid = :new.testerid;

  IF val = 3 THEN
    RAISE_APPLICATION_ERROR(-20202,
                            'Each tester may have three records at most !');
  END IF;
END;
/

Demo

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270031

I would recommend the following approach:

  1. In tester add a count column.
  2. In tester add a check constraint: check (testing <= 3).
  3. Add triggers on testing to keep the count up-to-date.

I think this is the simplest method for keeping a maximum number of records in Oracle.

Upvotes: 1

Related Questions