drop table b;
drop table a;

create table a (
  a1 integer primary key,
  a2 integer
);

create table b (
  b1 integer primary key,
  b2 integer
);

insert into a values (1, 3);
insert into a values (2, 6);
insert into b values (13, 1);
insert into b values (14, 1);
insert into b values (15, 1);

create trigger OnDelSetNull
  before delete on a
  for each row
  begin
    update b 
      set b2 = null
      where b2 = :old.a1;
  end;
/

create or replace trigger OnInsertOrUpdCheck
  before insert or update of b2 on B
  for each row
  when (new.b2 is not null)
declare
  ile integer;
begin
  select count(*) into ile
    from a
    where a1 = :new.b2;
  if (ile = 0) then
    raise_application_error(-20001, 'Brak rekordu nadrzednego');
  end if;
end;
/

create or replace trigger OnUpdCascade
  before update of a1 on A 
  for each row
begin
  update b set b2 = :new.a1
    where b2 = :old.a1;
end;
/

update a set a1 = 5 where a1 = 1;
