CREATE DOMAIN DMN_BOOL_2 AS
SMALLINT
DEFAULT 0
NOT NULL
CHECK (VALUE IN (0,1));

CREATE TABLE TBL_ADDRESS (
    ID                    INTEGER NOT NULL,
    FK_PEOPLE             INTEGER NOT NULL,
    PERMANENT_ADDRESS     DMN_BOOL_2 DEFAULT 1,
    CURRENT_ADDRESS       DMN_BOOL_2 DEFAULT 1,
COUNTRY               DMN_STRING_100
);

ALTER TABLE TBL_ADDRESS ADD CONSTRAINT PK_TBL_ADDRESS PRIMARY KEY (ID);

SET TERM ^ ;
CREATE OR ALTER TRIGGER TBL_ADDRESS_BIU3 FOR TBL_ADDRESS
ACTIVE BEFORE INSERT OR UPDATE POSITION 3
AS
begin
IF (NEW.PERMANENT_ADDRESS = 1) /* если вставляемая/изменяемая запись содержит флаг прописки */
  THEN
    BEGIN
      IF (NEW.CURRENT_ADDRESS = 1) /* если вставляемая/изменяемая запись содержит флаг текущего места жительства */
        THEN /* проверяем, есть для для вставляемого FK_PEOPLE аналогичные записи для обоих флагов (PERMANENT_ADDRESS = 1 и CURRENT_ADDRESS = 1) */
          BEGIN
            IF (EXISTS(
                  SELECT A.ID
                  FROM TBL_ADDRESS A
                  WHERE ((A.FK_PEOPLE = NEW.FK_PEOPLE) AND (A.PERMANENT_ADDRESS  = 1) AND (A.CURRENT_ADDRESS = 1))
                      )
                )
              THEN /* сбрасываем флаги обоих признаков */
                BEGIN
                  UPDATE TBL_ADDRESS A
                  SET A.PERMANENT_ADDRESS = 0,
                      A.CURRENT_ADDRESS = 0
                  WHERE (A.FK_PEOPLE = NEW.FK_PEOPLE);
                END
          END
        ELSE /* проверяем, есть для для вставляемого FK_PEOPLE аналогичные записи для флага прописки(PERMANENT_ADDRESS = 1) */
          BEGIN
            IF (EXISTS(
                  SELECT A.ID
                  FROM TBL_ADDRESS A
                  WHERE ((A.FK_PEOPLE = NEW.FK_PEOPLE) AND (A.PERMANENT_ADDRESS  = 1))
                      )
                )
              THEN /* сбрасываем флаг прописки */
                BEGIN
                  UPDATE TBL_ADDRESS A
                  SET A.PERMANENT_ADDRESS = 0
                  WHERE ((A.FK_PEOPLE = NEW.FK_PEOPLE) AND (A.PERMANENT_ADDRESS = 1));
                END
          END
    END
  ELSE
    IF (NEW.CURRENT_ADDRESS = 1) /* если вставляемая/изменяемая запись содержит флаг текущего места жительства */
      THEN /* проверяем, есть для для вставляемого FK_PEOPLE аналогичные записи флага текущего места жительства(CURRENT_ADDRESS = 1) */
        BEGIN
            IF (EXISTS(
                  SELECT A.ID
                  FROM TBL_ADDRESS A
                  WHERE ((A.FK_PEOPLE = NEW.FK_PEOPLE) AND (A.CURRENT_ADDRESS  = 1))
                      )
                )
              THEN /* сбрасываем флаг текущего места жительства */
                BEGIN
                  UPDATE TBL_ADDRESS A
                  SET A.CURRENT_ADDRESS = 0
                  WHERE ((A.FK_PEOPLE = NEW.FK_PEOPLE) AND (A.CURRENT_ADDRESS = 1));
                END
        END
end
^
SET TERM ; ^

INSERT INTO TBL_ADDRESS (ID, FK_PEOPLE, PERMANENT_ADDRESS, CURRENT_ADDRESS, COUNTRY) VALUES (NULL, 1, 1, 1, 'Россия');
COMMIT;

INSERT INTO TBL_ADDRESS (ID, FK_PEOPLE, PERMANENT_ADDRESS, CURRENT_ADDRESS, COUNTRY) VALUES (NULL, 1, 0, 0, 'Уганда');
COMMIT;

INSERT INTO TBL_ADDRESS (ID, FK_PEOPLE, PERMANENT_ADDRESS, CURRENT_ADDRESS, COUNTRY) VALUES (NULL, 1, 1, 0, 'Украина');
COMMIT;

Too many concurrent executions of the same request.
Too many concurrent executions of the same request.
At trigger 'TBL_ADDRESS_BIU3' line: 53, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 35, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 53, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 35, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 53, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 35, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 53, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 35, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 53, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 35, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 53, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 35, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 53, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 35, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 53, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 35, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 53, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 35, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 53, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 35, col: 19
At trigger 'TBL_ADDRESS_BIU3' line: 53, col: 19
At trigger '...
 

SET TERM ^ ;
CREATE OR ALTER TRIGGER TBL_ADDRESS_AIU1 FOR TBL_ADDRESS
ACTIVE AFTER INSERT OR UPDATE POSITION 1
AS
begin
IF (NEW.PERMANENT_ADDRESS = 1) THEN /* если вставляемая/изменяемая запись содержит флаг прописки */
  BEGIN
    IF (EXISTS(
          SELECT RDB$DB_KEY
          FROM TBL_ADDRESS A
          WHERE ((A.FK_PEOPLE = NEW.FK_PEOPLE) AND (OLD.PERMANENT_ADDRESS  = 1))
              )
        )
      THEN /* сбрасываем флаг прописки */
        BEGIN
          UPDATE TBL_ADDRESS A
          SET A.PERMANENT_ADDRESS = 0
          WHERE ((A.FK_PEOPLE = NEW.FK_PEOPLE) AND (OLD.PERMANENT_ADDRESS = 1));
        END
  END
end
^
SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER TRIGGER TBL_ADDRESS_AIU0 FOR TBL_ADDRESS
ACTIVE AFTER INSERT OR UPDATE POSITION 0
AS
begin

IF ((NEW.PERMANENT_ADDRESS = 1) /* если у новой/изменяемой записи есть флаг прописки */
    AND
     /* для текущего FK_PEOPLE уже есть записи с таким же флагом */
    EXISTS(
      SELECT A.ID
      FROM TBL_ADDRESS A
      WHERE ((A.FK_PEOPLE = NEW.FK_PEOPLE) AND (A.PERMANENT_ADDRESS  = 1) AND (A.RDB$DB_KEY <> NEW.RDB$DB_KEY))
          )
    )
  THEN /* сбрасываем флаг прописки у остальных, кроме вставляемой/изменяемой */
    BEGIN
      UPDATE TBL_ADDRESS A
      SET A.PERMANENT_ADDRESS = 0
      WHERE ((A.FK_PEOPLE = NEW.FK_PEOPLE) AND (A.PERMANENT_ADDRESS  = 1) AND (A.RDB$DB_KEY <> NEW.RDB$DB_KEY));
    END
--------------------------------------------------------------
IF ((NEW.CURRENT_ADDRESS = 1) /* если у новой/изменяемой записи есть флаг текущего места жительства */
    AND
     /* для текущего FK_PEOPLE уже есть записи с таким же флагом */
    EXISTS(
      SELECT A.ID
      FROM TBL_ADDRESS A
      WHERE ((A.FK_PEOPLE = NEW.FK_PEOPLE) AND (A.CURRENT_ADDRESS  = 1) AND (A.RDB$DB_KEY <> NEW.RDB$DB_KEY))
          )
    )
  THEN /* сбрасываем флаг текущего места жительства у остальных, кроме вставляемой/изменяемой */
    BEGIN
      UPDATE TBL_ADDRESS A
      SET A.CURRENT_ADDRESS = 0
      WHERE ((A.FK_PEOPLE = NEW.FK_PEOPLE) AND (A.CURRENT_ADDRESS  = 1) AND (A.RDB$DB_KEY <> NEW.RDB$DB_KEY));
    END
end
^

SET TERM ; ^