1. Prepare the schema

create sequence test_gen;

recreate table test_row
(id int not null,
 did int not null,
 pid int not null,
 dep int not null
);
alter table test_row add constraint pk_test_row primary key(id);
create unique index ix_test_row1 on test_row(did, pid, dep);
commit;

insert into test_row(id, did, pid,dep) values(1, 2, 3, 4);
commit;


2. test

execute block returns(id int, did int, dep int, pid int)
as
declare variable xid int;
begin
  select id,did, pid,dep
    from test_row
   where id=(select min(id) from test_row)
    into :xid, :did, pid, :dep;

  while (1=1) do
  begin
    delete from test_row r where r.id = :xid;
    
    insert into test_row(id, did, dep, pid)
    values (gen_id(test_gen, 1), :did, :dep, :pid);

    suspend;

  when any do
    exception;
  end
end

  Raised (expected) error:

Invalid insert or update value(s): object columns are constrained - no 2 table rows can have duplicate column values.
attempt to store duplicate value (visible to active transactions) in unique index "IX_TEST_ROW1".

After ROLLBACK we have

Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements.
internal Firebird consistency check (Too many savepoints (287), file: tra.cpp line: 1341).



3. Create stored procedure with the same contents as EXECUTE BLOCK above

create or alter procedure sp_test
  returns(id int, did int, dep int, pid int)
as
declare variable xid int;
begin
  select id,did, pid,dep
    from test_row
   where id=(select min(id) from test_row)
    into :xid, :did, pid, :dep;

  while (1=1) do
  begin
    delete from test_row r where r.id = :xid;
    
    insert into test_row(id, did, dep, pid)
    values (gen_id(test_gen, 1), :did, :dep, :pid);

    suspend;

  when any do
    exception;
  end
end

4. select * from sp_test

  Raised (expected) error:

Invalid insert or update value(s): object columns are constrained - no 2 table rows can have duplicate column values.
attempt to store duplicate value (visible to active transactions) in unique index "IX_TEST_ROW1".
At procedure 'SP_TEST' line: 15, col: 5.
At procedure 'SP_TEST' line: 20, col: 12.

rollback is OK