Контроль данных

CREATE TABLE USER$MAIL_BLOCK
(
  MAILPART TTEXT255 NOT NULL,
  TYPEBLOCK INTEGER DEFAULT 0,
  MODIFYDATE TDATE,
  REPL$ID REPL$IDENT,
  REPL$GRPID REPL$IDENT,
  CONSTRAINT PK_USER$MAIL_BLOCK PRIMARY KEY (MAILPART)
);

CREATE OR ALTER TRIGGER USER$MAIL_LOG_MAILBLOCK FOR MAIL_LOG
active after UPDATE POSITION 99
AS
BEGIN
  IF (EXISTS(SELECT * FROM repl$getgrpid g JOIN filials f ON f.grpid=g.repl$grpid WHERE f.ismain=1)
      AND NEW.errortext = 'N/A') THEN
  BEGIN
    -- вставка адреса в блок
    INSERT INTO USER$mail_block(mailpart, typeblock, modifydate)
    SELECT m.mailaddress, 1, CURRENT_TIMESTAMP FROM mail_log m
      LEFT JOIN USER$mail_block b ON UPPER(b.mailpart)=UPPER(m.mailaddress)
     WHERE m.mailid = NEW.mailid AND b.mailpart IS NULL;
    -- обновление остальных записей
    UPDATE mail_log m SET m.mailstatus = COALESCE(m.mailstatus, 10), m.errortext = 'N/A'
     WHERE CAST(m.createdate AS DATE) >= CAST(NEW.createdate AS DATE)
       AND UPPER(m.mailaddress) = UPPER(NEW.mailaddress) AND m.mailid!=NEW.mailid AND m.errortext!='N/A';
  END
END

CREATE OR ALTER TRIGGER USER$MAIL_BLOCK_SET_CLIENTS FOR USER$MAIL_BLOCK
active after INSERT OR UPDATE POSITION 99
AS
DECLARE pcode TYPE OF COLUMN clients.pcode;
BEGIN
  FOR SELECT c.pcode FROM clients c WHERE UPPER(c.clmail) LIKE UPPER(NEW.mailpart)
    INTO pcode do
  BEGIN
    UPDATE clients SET repl$id=repl$id WHERE pcode= :pcode;
  END
END

CREATE OR ALTER TRIGGER USER$CLIENTS_MAIL_BLOCK FOR CLIENTS
active BEFORE INSERT OR UPDATE POSITION 90
AS
BEGIN
  IF (EXISTS(SELECT * FROM  USER$mail_block b WHERE UPPER(NEW.clmail) LIKE UPPER(b.mailpart))) THEN
  BEGIN
    NEW.clmail = 'Введеный адрес - не существует';
    NEW.modifydate = CURRENT_TIMESTAMP;
  END
END