Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table BLATABLE
(
    ID            NUMBER(19) GENERATED ALWAYS AS IDENTITY primary key,
    CLIENTID      NUMBER(10),
    CREATED       TIMESTAMP(6),
    STATUS        VARCHAR2(255 char)
);
CREATE OR REPLACE TRIGGER blatrigger
  AFTER INSERT ON BLATABLE
  REFERENCING NEW AS NEW OLD AS OLD

DECLARE
  counter        INT;
  exception_name EXCEPTION;
  PRAGMA EXCEPTION_INIT(exception_name, -20999);
BEGIN
  select MAX(COUNT(*))
    INTO counter
    FROM BLATABLE
   WHERE STATUS = 'PENDING'
   GROUP BY CLIENTID;
     
  IF counter > 1 THEN
    RAISE exception_name;
  END IF;
END;
/
BEGIN
INSERT INTO BLATABLE(CLIENTID,STATUS) VALUES(1,'PENDING');
INSERT INTO BLATABLE(CLIENTID,STATUS) VALUES(2,'KILLED');
INSERT INTO BLATABLE(CLIENTID,STATUS) VALUES(3,'ACTIVE');
END;
/
1 rows affected

INSERT INTO BLATABLE(CLIENTID,STATUS) VALUES(1,'PENDING');
ORA-20999: 
ORA-06512: at "FIDDLE_COHOYMFFKYHUREXRLPYD.BLATRIGGER", line 13
ORA-04088: error during execution of trigger 'FIDDLE_COHOYMFFKYHUREXRLPYD.BLATRIGGER'

INSERT INTO BLATABLE(CLIENTID,STATUS) VALUES(3,'ACTIVE');
1 rows affected

SELECT * FROM BLATABLE;


ID
CLIENTID
CREATED
STATUS


1
1
null
PENDING

2
2
null
KILLED

3
3
null
ACTIVE

5
3
null
ACTIVE