INSERT INTO `local_auth_account_journal` (`id`, `account_id`, `amount`, `date`, `source_type`, `source_id`, `cash_id`, `comment`, `balance`, `action_date`) VALUES
(9912, 4909, 500, '2013-07-31 15:12:34', 1, '4', 1002006, 'проблемы со связью, не получилось online проводка - сделано offline', 500, '2013-08-05 15:34:05');

INSERT INTO `local_auth_account_journal` (`id`, `account_id`, `amount`, `date`, `source_type`, `source_id`, `cash_id`, `comment`, `balance`, `action_date`) VALUES
(9913, 4909, 0, '2013-08-02 11:43:45', 1, '5', 1002006, 'проблемы со связью, не получилось online проводка - сделано offline', 500, '2013-08-05 15:34:05');

CREATE DEFINER = 'ukm_server'@'localhost' TRIGGER `local_auth_account_journal_before_ins_tr` BEFORE INSERT ON `local_auth_account_journal`
  FOR EACH ROW
BEGIN
    DECLARE lk INT;
    DECLARE _credit DECIMAL(20,2);
    DECLARE dtClose DATETIME;
    DECLARE  acc_name VARCHAR(40);

    if (NEW.amount = 0) THEN
        set @acc_error_str = 'Попытка проводки на нулевую сумму';
        select no_such_field INTO lk;
    end if;

    select credit,closed,`name`
    into  _credit, dtClose, acc_name
    from local_auth_account
    where  id = NEW.account_id;

    if (not dtClose is null and dtClose < NEW.date) then
          set @acc_error_str = CONCAT('Счет ',acc_name,' закрыт ',DATE_FORMAT(dtClose,'%d.%m.%Y'));
          select no_such_field INTO lk;
    end if;

    set NEW.action_date = NOW();
END;

CREATE TABLE `local_auth_account_journal` (
  `id` INTEGER(11) NOT NULL AUTO_INCREMENT,
  `account_id` INTEGER(11) NOT NULL DEFAULT 0,
  `amount` DECIMAL(20,2) DEFAULT NULL,
  `date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `source_type` SMALLINT(6) UNSIGNED NOT NULL DEFAULT 0,
  `source_id` VARCHAR(40) COLLATE utf8_general_ci NOT NULL DEFAULT '0',
  `cash_id` INTEGER(11) NOT NULL DEFAULT 0,
  `comment` TEXT COLLATE utf8_general_ci,
  `balance` DECIMAL(20,2) DEFAULT NULL,
  `action_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `date` (`date`, `id`) USING BTREE,
  KEY `local_auth_account_journal_FK1` (`account_id`) USING BTREE,
  KEY `account_id_date` (`account_id`, `date`) USING BTREE,
  KEY `source_type` (`account_id`, `source_type`) USING BTREE,
  KEY `source_type_date` (`account_id`, `source_type`, `date`) USING BTREE,
  KEY `account_id_id` (`account_id`, `id`) USING BTREE,
  CONSTRAINT `local_auth_account_journal_fk` FOREIGN KEY (`account_id`) REFERENCES `local_auth_account` (`id`)
) ENGINE=InnoDB
AUTO_INCREMENT=42608 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
COMMENT='Проводки по счетам'
;

CREATE DEFINER = 'ukm_server'@'localhost' TRIGGER `local_auth_account_journal_before_ins_tr` BEFORE INSERT ON `local_auth_account_journal`
  FOR EACH ROW
BEGIN
    DECLARE lk INT;
    DECLARE _credit DECIMAL(20,2);
    DECLARE dtClose DATETIME;
    DECLARE  acc_name VARCHAR(40);

    if (NEW.amount = 0) THEN
        set @acc_error_str = 'Попытка проводки на нулевую сумму';
        select no_such_field INTO lk;
    end if;

    select credit,closed,`name`
    into  _credit, dtClose, acc_name
    from local_auth_account
    where  id = NEW.account_id;

    if (not dtClose is null and dtClose < NEW.date) then
          set @acc_error_str = CONCAT('Счет ',acc_name,' закрыт ',DATE_FORMAT(dtClose,'%d.%m.%Y'));
          select no_such_field INTO lk;
    end if;

    set NEW.action_date = NOW();
END;

CREATE DEFINER = 'ukm_server'@'localhost' TRIGGER `local_auth_account_journal_before_upd_tr` BEFORE UPDATE ON `local_auth_account_journal`
  FOR EACH ROW
BEGIN
	DECLARE _credit DECIMAL(20,2);
    DECLARE lk INT;
    DECLARE dtClose DATETIME;
    DECLARE  acc_name VARCHAR(40);

    select credit,closed,`name`
    into  _credit, dtClose, acc_name
    from local_auth_account
    where  id = NEW.account_id;

    if (not dtClose is null and dtClose < NEW.date) then
        set @acc_error_str = CONCAT('Счет ',acc_name,' закрыт ',DATE_FORMAT(dtClose,'%d.%m.%Y'));
        select no_such_field INTO lk;
    end if;
END;