Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table owner(
id int primary key,
name varchar(10));
create table dog(
name varchar(10) primary key,
oid int ,
constraint fk_dog_owner foreign key 
     (oid) references owner(id)
);
CREATE OR REPLACE  TRIGGER dog_max_two
BEFORE INSERT ON dog
    FOR EACH ROW 
DECLARE
  dogCount number(10);
BEGIN 
  Select count(name) INTO dogCount
  From dog where oid = :NEW.oid;
  IF( dogCount > 1 )
    THEN
      RAISE_APPLICATION_ERROR( -20001,
        'maximum 2 dogs per person');
  END IF;
END;
/
INSERT INTO owner VALUES (1,'Bill');
1 rows affected

INSERT INTO dog VALUES ('Rover', 1);
1 rows affected

INSERT INTO dog VALUES ('Boy', 1);
1 rows affected

INSERT INTO dog VALUES ('Alfie', 1);
ORA-20001: maximum 2 dogs per person
ORA-06512: at "FIDDLE_HZTJGHLANPRDIFXWPMZS.DOG_MAX_TWO", line 8
ORA-04088: error during execution of trigger 'FIDDLE_HZTJGHLANPRDIFXWPMZS.DOG_MAX_TWO'

select * from dog;


NAME
OID


Rover
1

Boy
1