CONNECT TO SAMPLE
@

DROP TRIGGER	kernel.BDTrnDays@
DROP TRIGGER	kernel.BDTrnDebet@
DROP TRIGGER	kernel.BDTrnCredit@
DROP TRIGGER	kernel.BITrnDays@
DROP TRIGGER	kernel.BITrnDebet@
DROP TRIGGER	kernel.BITrnCredit@
DROP TRIGGER	kernel.BUTrn@
DROP TRIGGER	kernel.ADTrn@
DROP TRIGGER	kernel.AITrn@

DROP TRIGGER 	kernel.ADJrn@
DROP TRIGGER 	kernel.BDJrn@
DROP TRIGGER 	kernel.BIJrn@
DROP TRIGGER	kernel.AIJrn@

DROP TRIGGER    kernel.BUAccount@
DROP TRIGGER	kernel.AIAccount@
DROP TRIGGER	kernel.BIAccount@

DROP TRIGGER	kernel.BIStuff@

DROP TRIGGER	kernel.AIOverdraft@
DROP TRIGGER	kernel.ADOverdraft@

DROP TRIGGER	kernel.BIDocumentDayCheck@
DROP TRIGGER	kernel.BDDocumentDayCheck@
DROP TRIGGER	kernel.BUDocument@
DROP TRIGGER 	kernel.BIDocument@
DROP TRIGGER	kernel.ADDocument@
DROP TRIGGER	kernel.AIDocument@

DROP TABLE		kernel.Overdraft@
DROP TABLE		kernel.Trn@
DROP TABLE		kernel.Document@
DROP TABLE 		kernel.Days@
DROP TABLE		kernel.Jrn@
DROP TABLE		kernel.Account@
DROP TABLE		kernel.Stuff@

CREATE TABLE kernel.Stuff (
	Stuff	INTEGER NOT NULL,
	Name	CHAR( 35 ),
    AddCnt  INTEGER,
    DelCnt  INTEGER,
	CONSTRAINT Pk PRIMARY KEY (STUFF)
)
@

CREATE TRIGGER kernel.BIStuff 
NO CASCADE BEFORE INSERT ON kernel.Stuff
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
	SET N.AddCnt= 0 ;
    SET N.DelCnt= 0 ;
END
@


ECHO  -------------------------@
 
ECHO Импорт учетных плоскостей@
ECHO@
IMPORT FROM _Stuff.del OF DEL COMMITCOUNT  100 
	INSERT INTO kernel.Stuff (Stuff,Name)
@
ECHO  -------------------------@
 
ECHO@
ECHO@


 ----------------------------------------------------------------
 
 --	Stuff		Учетная плоскость
 
 --	Account		Аналитический счет
 
 --  Opened		Дата открытия/создания счета
 
 --  Closed		Дата закрытия счета
 
 --	Type		Тип счета Активный,Пассивный, Астивно-пассивный
 
 --	Overdraft	Глубина овердрафта
 
 --	Active		Входящий активный остаток
 
 --	Passive		Входящий пассивный остаток
 
 --	Debet		обороты по дебету
 
 --	Credit		обороты по кредиту
 
 ----------------------------------------------------------------
 
CREATE TABLE kernel.Account (
	Stuff	INTEGER NOT NULL,
	Account	INTEGER NOT NULL,
	Closed	DATE,
	Type	CHAR( 1 ) CHECK (Type IN ('A','P','M')),
	Overdraft	DECIMAL( 17 , 2 ) CHECK (Overdraft>= 0 )  DEFAULT  0 ,
	Active		DECIMAL( 17 , 2 ) CHECK (Active>= 0 )		DEFAULT  0 ,
	Passive		DECIMAL( 17 , 2 ) CHECK (Passive>= 0 )	DEFAULT  0 ,
	Debet		DECIMAL( 17 , 2 ) CHECK (Debet>= 0 )		DEFAULT  0 ,
	Credit		DECIMAL( 17 , 2 ) CHECK (Credit>= 0 )		DEFAULT	 0 ,
	DCnt		INTEGER DEFAULT  0 ,
	CCnt		INTEGER DEFAULT  0 ,
	CONSTRAINT	AtStuff FOREIGN KEY (Stuff) 
		REFERENCES kernel.Stuff (Stuff) ON DELETE RESTRICT,
	CONSTRAINT  AtAPMCheck CHECK (
		(	
			(Type='A' AND Active>= 0 . 00  AND Passive= 0 . 00 ) 
			OR (Type='P') 
			OR (Type='M')
		)
		AND (Active= 0 . 00  OR Passive= 0 . 00 )
	),
	CONSTRAINT  AtBalance CHECK (
		(Type='A' AND (Active-Passive+Debet-Credit)>= 0 . 00 )
		OR (Type='P' AND (Passive-Active+Credit-Debet+Overdraft)>= 0 . 00 )
		OR (Type='M')
	),
	CONSTRAINT	Pk PRIMARY KEY (Stuff,Account)
)
@
CREATE UNIQUE INDEX kernel.IAccountStuff
	ON kernel.Account (Stuff DESC,Account DESC)
@

 --------------------------------------------------------------------
 
 --  Журнал остатков по аналитическому счету
 
 --------------------------------------------------------------------
 
 --  Stuff		
 
 --  Account 	
 
 --  Dt			
 
 --  Type		
 
 --  Overdraft	
 
 --	Active		
 
 --	Passive		
 
 --	Debet		
 
 --	Credit		
 
 --	DCnt		
 
 --	CCnt		
 
 --------------------------------------------------------------------
 
CREATE TABLE kernel.Jrn (
    Stuff		INTEGER,
    Account 	INTEGER,
    Dt			DATE,
    Type		CHAR( 1 ),
    Overdraft	DECIMAL( 17 , 2 ),
	Active		DECIMAL( 17 , 2 ) CHECK (Active>= 0 )		DEFAULT  0 ,
	Passive		DECIMAL( 17 , 2 ) CHECK (Passive>= 0 )	DEFAULT  0 ,
	Debet		DECIMAL( 17 , 2 ) CHECK (Debet>= 0 )		DEFAULT  0 ,
	Credit		DECIMAL( 17 , 2 ) CHECK (Credit>= 0 )		DEFAULT	 0 ,
	DCnt		INTEGER DEFAULT  0 ,
	CCnt		INTEGER DEFAULT  0 ,
	CONSTRAINT	AtStuffAccount FOREIGN KEY (Stuff,Account)
		REFERENCES kernel.Account (Stuff,Account) ON DELETE RESTRICT,
	CONSTRAINT  AtAPMCheck CHECK (
		(	
			(Type='A' AND Active>= 0 . 00  AND Passive= 0 . 00 ) 
			OR (Type='P') 
			OR (Type='M')
		)
		AND (Active= 0 . 00  OR Passive= 0 . 00 )
	),
	CONSTRAINT  AtBalance CHECK (
		(Type='A' AND (Active-Passive+Debet-Credit)>= 0 . 00 )
		OR (Type='P' AND (Passive-Active+Credit-Debet+Overdraft)>= 0 . 00 )
		OR (Type='M')
	)
)
@

CREATE UNIQUE INDEX kernel.IJrnSAD ON kernel.Jrn (Stuff,Account,Dt) ALLOW REVERSE SCANS@
CREATE UNIQUE INDEX kernel.IJrnDSA ON kernel.Jrn (Dt,Stuff,Account)@

 ----------------------------------------------------------------
 
 -- Контроль допустимых значений параметров счета
 
 -- Если овердрафт NULL тогда - 0
 
 ----------------------------------------------------------------
 
CREATE TRIGGER kernel.BUAccount
NO CASCADE BEFORE UPDATE ON kernel.Account
REFERENCING NEW AS N OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
	SET N.Overdraft= 
		CASE 
			WHEN (N.Overdraft IS NULL) 
			THEN  0  ELSE N.Overdraft
        END;
END
@

 ----------------------------------------------------------------
 
 -- При добавлении новой записи в таблицу счетов
 
 -- обнуляются поля Overdraft,Active,Passive,Debet,Credit
 
 ----------------------------------------------------------------
 
CREATE TRIGGER kernel.BIAccount 
NO CASCADE BEFORE INSERT ON kernel.Account
REFERENCING NEW AS N 
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
	SET N.Closed=NULL;
	SET N.Overdraft= 0 ;
	SET N.Active= 0 ;
	SET N.Passive= 0 ;
	SET N.Debet= 0 ;
	SET N.Credit= 0 ;
	SET N.DCnt= 0 ;
	SET N.CCnt= 0 ;
END
@

CREATE TRIGGER kernel.AIAccount 
AFTER INSERT ON kernel.Account
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
UPDATE kernel.Stuff S
    SET S.AddCnt=S.AddCnt+ 1  WHERE S.Stuff=N.Stuff
@


ECHO  ---------------------------@
 
ECHO Импорт аналитических счетов@
ECHO@
IMPORT FROM _Account.del OF DEL COMMITCOUNT  100 
	INSERT INTO kernel.Account (Stuff,Account,Type)
@
ECHO  ---------------------------@
 
ECHO@
ECHO@

 ----------------------------------------------------------------
 
 --	Dt			Дата проведения операции
 
 --	Closed		Признак открыт/закрыт
 
 --	AddCnt	    Количество добавленных документов 
 
 --	DelCnt		Количество удаленных документов
 
 --	DebetCnt	Количество свернутых в журнал проводок по дебету
 
 --	CreditCnt	Количество свернутых в журнал проводок по кредиту
 
 --	TrnCnt		Количество тpанзакций * 2 в дне
 
 ----------------------------------------------------------------
 
CREATE TABLE kernel.Days (
	Dt			DATE NOT NULL, 
	Closed		CHAR( 1 ) CHECK (Closed in ('Y','N')) DEFAULT 'N', 
	AddCnt		INTEGER	DEFAULT  0 ,
	DelCnt		INTEGER DEFAULT  0 ,
	DebetCnt	INTEGER DEFAULT  0 ,
	CreditCnt	INTEGER DEFAULT  0 ,
	TrnCnt		INTEGER DEFAULT  0 ,
	CONSTRAINT  Pk PRIMARY KEY (Dt)
)
@

INSERT INTO kernel.Days (Dt,Closed) VALUES (CURRENT DATE,'N')
@





 ----------------------------------------------------------------
 
 --	Dt		Дата к которой относится документ
 
 --	DocID	Идентификатор документа в дне (порядковый номер)
 
 --	UserID	Пользователь, создавший документ
 
 --	DocType Тип документа
 
 --	DocTS	Дата и время создания документа
 
 ----------------------------------------------------------------
 
CREATE TABLE kernel.Document (
	Dt		DATE NOT NULL,
	DocID	INTEGER NOT NULL,
	UserID	CHAR( 12 ),
	DocType INTEGER,
	DocTS	TIMESTAMP,
	CONSTRAINT AtDt FOREIGN KEY (Dt) 
		REFERENCES kernel.Days (Dt) ON DELETE RESTRICT,
	CONSTRAINT Pk PRIMARY KEY (Dt,DocID)
)
@


 ----------------------------------------------------------------
 
 -- Триггер на добавление нового документа
 
 -- Увеличиваем количество введенных документов
 
 ----------------------------------------------------------------
 
CREATE TRIGGER kernel.AIDocument
AFTER INSERT ON kernel.Document
REFERENCING NEW AS N 
FOR EACH ROW MODE DB2SQL
	UPDATE kernel.Days Days set Days.AddCnt=Days.AddCnt+ 1 
       WHERE Days.Dt=N.Dt
@

 ----------------------------------------------------------------
 
 -- Триггер на удаление нового документа
 
 -- Уменьшаем количество введенных документов
 
 ----------------------------------------------------------------
 
CREATE TRIGGER kernel.ADDocument
AFTER DELETE ON kernel.Document
REFERENCING OLD AS O 
FOR EACH ROW MODE DB2SQL
	UPDATE kernel.Days Days set Days.DelCnt=Days.DelCnt+ 1 
       WHERE Days.Dt=O.Dt
@

 ----------------------------------------------------------------
 
 -- Триггер на добавление нового документа
 
 -- Контроль что операционный день открыт
 
 ----------------------------------------------------------------
 
CREATE TRIGGER kernel.BIDocumentDayCheck 
NO CASCADE BEFORE INSERT ON kernel.Document
REFERENCING NEW AS Doc
FOR EACH ROW MODE DB2SQL
WHEN
	( 
		(SELECT Days.Closed 
			FROM kernel.Days Days 
			WHERE Days.Dt=Doc.Dt) = 'Y' 
	) SIGNAL SQLSTATE '75002' ('Попытка добавить документ в закрытый операционный день')
@

 ----------------------------------------------------------------
 
 -- Триггер на добавление нового документа
 
 -- Контроль что операционный день открыт
 
 ----------------------------------------------------------------
 
CREATE TRIGGER kernel.BDDocumentDayCheck 
NO CASCADE BEFORE DELETE ON kernel.Document 
REFERENCING OLD AS Doc
FOR EACH ROW MODE DB2SQL
WHEN
	(
		(SELECT Days.Closed 
			FROM kernel.Days Days 
			WHERE Days.Dt=Doc.Dt) = 'Y' 
	) SIGNAL SQLSTATE '75003' ('Попытка удаления документа из закрытого операционного дня')
@

 ----------------------------------------------------------------
 
 -- Триггер на добавление нового документа
 
 -- Контроль кто и когда добавил документ
 
 ----------------------------------------------------------------
 
CREATE TRIGGER kernel.BIDocument 
NO CASCADE BEFORE INSERT ON kernel.Document 
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
	SET N.UserID=USER;
	SET DocTS=CURRENT TIMESTAMP;
END
@

 ----------------------------------------------------------------
 
 -- После того, как документ внесен нельзя менять его параметы
 
 ----------------------------------------------------------------
 
CREATE TRIGGER kernel.BUDocument
NO CASCADE BEFORE UPDATE ON kernel.Document
FOR EACH ROW MODE DB2SQL
	SIGNAL SQLSTATE '75001' ('Hельзя изменять параметры документа')
@



 ----------------------------------------------------------------
 
 --  Проводки
 
 ----------------------------------------------------------------
 
CREATE TABLE kernel.Trn (
	Dt			DATE NOT NULL,
	DocID		INTEGER NOT NULL,
	TrnID		SMALLINT,
	Stuff		INTEGER,
	DAccount	INTEGER,
	CAccount	INTEGER,
	Summa		DECIMAL( 17 , 2 ) CHECK (Summa>= 0 ), 
	CONSTRAINT	AtDocument FOREIGN KEY (Dt,DocID)
		REFERENCES kernel.Document ON DELETE CASCADE,
	CONSTRAINT	AtStuff FOREIGN KEY (Stuff)
		REFERENCES kernel.Stuff ON DELETE RESTRICT,
	CONSTRAINT	AtDAccount FOREIGN KEY (Stuff,DAccount)
		REFERENCES kernel.Account (Stuff,Account),
	CONSTRAINT	AtCAccount FOREIGN KEY (Stuff,CAccount)
		REFERENCES kernel.Account (Stuff,Account)
)
@

CREATE UNIQUE INDEX kernel.IDTrn ON kernel.Trn (Stuff,DAccount,Dt,DocID,TrnID)@
CREATE UNIQUE INDEX kernel.ICTrn ON kernel.Trn (Stuff,CAccount,Dt,DocID,TrnID)@

 ----------------------------------------------------------------
 
 -- Этот триггер выполняет изменение остатков
 
 -- при добавлении проводки
 
 ----------------------------------------------------------------
 
CREATE TRIGGER kernel.AITrn
AFTER INSERT ON kernel.Trn
REFERENCING NEW AS N 
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
	UPDATE kernel.Account Deb
		SET (Deb.Debet,Deb.DCnt)=(Deb.Debet+N.Summa,Deb.DCnt+ 1 )
		WHERE Deb.Stuff=N.Stuff AND Deb.Account=N.DAccount;
	UPDATE kernel.Account Cred
		SET (Cred.Credit,Cred.CCnt)=(Cred.Credit+N.Summa,Cred.CCnt+ 1 )
		WHERE Cred.Stuff=N.Stuff AND Cred.Account=N.CAccount;
	UPDATE kernel.Days
		SET TrnCnt=TrnCnt+ 2 
		WHERE Dt=N.Dt;
END
@

 ----------------------------------------------------------------
 
 -- Запpет на любые изменения в таблице Trn
 
 ----------------------------------------------------------------
 
CREATE TRIGGER kernel.BUTrn
	NO CASCADE BEFORE UPDATE ON kernel.Trn
	FOR EACH ROW MODE DB2SQL
	SIGNAL SQLSTATE '75004' ('Hельзя изменять таблицу TRN')
@

 ----------------------------------------------------------------
 
 -- Этот триггер выполняет изменение остатков
 
 -- при удалении проводки
 
 ----------------------------------------------------------------
 
CREATE TRIGGER kernel.ADTrn
AFTER DELETE ON kernel.Trn
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
	UPDATE kernel.Account Deb
		SET (Deb.Debet,Deb.DCnt)=(Deb.Debet-O.Summa,Deb.DCnt- 1 )
		WHERE Deb.Stuff=O.Stuff AND Deb.Account=O.DAccount;
	UPDATE kernel.Account Cred
		SET (Cred.Credit,Cred.CCnt)=(Cred.Credit-O.Summa,Cred.CCnt- 1 )
		WHERE Cred.Stuff=O.Stuff AND Cred.Account=O.CAccount;
	UPDATE kernel.Days
		SET TrnCnt=TrnCnt- 2 
		WHERE Dt=O.Dt;
END
@

 ----------------------------------------------------------------
 
 --	Stuff		Учетная плоскость
 
 --	Account		Аналитический счет
 
 --	Dt			Дата изменения
 
 --	Overdraft	Глубина овердрафта
 
 ----------------------------------------------------------------
 
CREATE TABLE kernel.Overdraft (
	Stuff		INTEGER NOT NULL,
	Account		INTEGER NOT NULL,
	Dt			DATE 	NOT NULL,
	Overdraft	DECIMAL( 17 , 2 ) CHECK (Overdraft >= 0 ),
	CONSTRAINT AtAccount FOREIGN KEY (Stuff,Account)
		REFERENCES kernel.Account (Stuff,Account) ON DELETE RESTRICT
)
@

CREATE UNIQUE INDEX kernel.IOverdraft 
	ON kernel.Overdraft (Stuff,Account,Dt) ALLOW REVERSE SCANS
@

 ----------------------------------------------------------------
 
 -- Изменение глубины овердрафта
 
 ----------------------------------------------------------------
 
CREATE TRIGGER kernel.AIOverdraft
AFTER INSERT ON kernel.Overdraft 
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (
	N.Dt=
	(SELECT 
			CASE 
				WHEN Overdraft.Dt IS NULL THEN N.Dt
				ELSE Overdraft.Dt
			END
		FROM kernel.Overdraft Overdraft 
		WHERE 
			Overdraft.Dt=
				(SELECT MAX(O.Dt) FROM kernel.Overdraft O WHERE 
							O.Stuff=N.Stuff AND O.Account=N.Account	) 
			AND Overdraft.Stuff=N.Stuff 
			AND Overdraft.Account=N.Account
	)
) 
	UPDATE kernel.Account Account 
			SET Account.Overdraft=N.Overdraft
		WHERE Account.Stuff=N.Stuff AND Account.Account=N.Account
@

 ----------------------------------------------------------------
 
 -- Изменение глубины овердрафта
 
 ----------------------------------------------------------------
 
IMPORT FROM _Overdraft.del OF DEL COMMITCOUNT  100 
	INSERT INTO kernel.Overdraft (Stuff,Account,Dt,Overdraft)
@

 ----------------------------------------------------------------
 
 -- Изменение глубины овердрафта
 
 ----------------------------------------------------------------
 
CREATE TRIGGER kernel.ADOverdraft
AFTER DELETE ON kernel.Overdraft
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
UPDATE kernel.Account SET Overdraft=
	(SELECT Overdraft FROM kernel.Overdraft 
		WHERE Stuff=O.Stuff
				AND Account=O.Account
				AND Dt=
						(SELECT MAX(Dt) FROM kernel.Overdraft
							WHERE Stuff=O.Stuff AND Account=O.Account)
	)
@


CREATE TRIGGER kernel.BIJrn
NO CASCADE BEFORE INSERT ON kernel.Jrn
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
      SET (N.Active,N.Passive,N.Type) =
          (SELECT A.Active,A.Passive,A.Type FROM kernel.Account A WHERE 
                  A.Stuff=N.Stuff AND A.Account=N.Account);
      SET (N.DCnt,N.Debet) =
          (SELECT COUNT(*),VALUE(SUM(Summa), 0 ) FROM kernel.Trn D WHERE
                  D.Stuff=N.Stuff  
                  AND D.DAccount=N.Account
                  AND D.Dt=N.Dt); 
      SET (N.CCnt,N.Credit) =
          (SELECT COUNT(*),VALUE(SUM(Summa), 0 ) FROM kernel.Trn C WHERE
                  C.Stuff=N.Stuff
                  AND C.CAccount=N.Account
                  AND C.Dt=N.Dt);
      		
END
@


 --Автоматическая коppекция Accounts пpи добавлении записи
 
 --в жуpнал остатков
 
CREATE TRIGGER kernel.AIJrn
   AFTER INSERT ON kernel.Jrn
   REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL
   BEGIN ATOMIC
   UPDATE kernel.Account A SET
      (A.Debet,A.Credit,A.Active,A.Passive,A.DCnt,A.CCnt)=(
      A.Debet-N.Debet,
      A.Credit-N.Credit,
      ROUND((ABS(A.Active+N.Debet-A.Passive-N.Credit)+A.Active+N.Debet-A.Passive-N.Credit)/ 2 , 2 ),
      ROUND((ABS(A.Passive+N.Credit-A.Active-N.Debet)+A.Passive+N.Credit-A.Active-N.Debet)/ 2 , 2 ),
      A.DCnt-N.DCnt,
      A.CCnt-N.CCnt
   ) WHERE A.Stuff=N.Stuff AND A.Account=N.Account;
   UPDATE kernel.Days SET (DebetCnt,CreditCnt)=
      (DebetCnt+N.DCnt,CreditCnt+N.CCnt) WHERE Dt=N.Dt;
   END
@


 --Пpовеpка на возможность удаления записи из жуpнала
 
 --Записи из жуpнала должны удаляться только по поpядку
 
 --Запpет на удаление самой пеpвой записи в жуpнале
 
CREATE TRIGGER kernel.BDJrn
   NO CASCADE BEFORE DELETE ON kernel.Jrn
   REFERENCING OLD AS O FOR EACH ROW MODE DB2SQL
   WHEN ( 
      (SELECT MAX(Dt) FROM kernel.Jrn A
          WHERE A.Stuff=O.Stuff AND A.Account=O.Account)<>O.Dt
   ) SIGNAL SQLSTATE '75008' ('Hельзя удалить эту запись из жуpнала')
@

 --Изменение Accounts после удаления записи из жуpнала
 
 --остатков
 
CREATE TRIGGER kernel.ADJrn
   AFTER DELETE ON kernel.Jrn
   REFERENCING OLD AS O FOR EACH ROW MODE DB2SQL
   BEGIN ATOMIC
   UPDATE kernel.Account A SET
      (A.Active,A.Passive,A.Debet,A.Credit,A.DCnt,A.CCnt)=
      (O.Active,O.Passive,O.Debet+A.Debet,O.Credit+A.Credit,
       	  A.DCnt+O.DCnt,A.CCnt+O.CCnt)
      WHERE A.Stuff=O.Stuff and A.Account=O.Account;
   UPDATE kernel.Days SET (DebetCnt,CreditCnt)= 
      (DebetCnt-O.DCnt,CreditCnt-O.CCnt) WHERE Dt=O.Dt;
   END
@

 ------------------------------------------------------------------------------
 
 --Запpет на добавление новой тpанзакции если
 
 --был создан жуpнал движения по дебетуемому или кpедитуемому
 
 --внутpеннему счету или опеpационный день закpыт
 
 ------------------------------------------------------------------------------
 
CREATE TRIGGER kernel.BITrnDays
   NO CASCADE BEFORE INSERT ON kernel.Trn
   REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL
   WHEN (
      (SELECT Closed FROM kernel.Days A WHERE A.Dt=N.Dt)='Y'
   ) SIGNAL SQLSTATE '75003' ('Пеpед добавлением тpанзакции')
@

CREATE TRIGGER kernel.BITrnDebet
   NO CASCADE BEFORE INSERT ON kernel.Trn
   REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL
   WHEN ( 
      N.Dt <= (SELECT VALUE(MAX(Dt),DATE('01.01.0001')) FROM kernel.Jrn D
                       WHERE D.Stuff=N.Stuff AND D.Account=N.DAccount )
   ) SIGNAL SQLSTATE '75001' ('Существует журнал остатков по дебетуемому счету')
@

CREATE TRIGGER kernel.BITrnCredit
   NO CASCADE BEFORE INSERT ON kernel.Trn
   REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL
   WHEN ( 
      N.Dt <= (SELECT VALUE(MAX(Dt),DATE('01.01.0001')) FROM kernel.Jrn C
                       WHERE C.Stuff=N.Stuff AND C.Account=N.CAccount )
   ) SIGNAL SQLSTATE '75002' ('Существует журнал остатков по кредитуемому счету')
@

 ------------------------------------------------------------------------------
 
 --Запpет на удаление тpанзакции если
 
 --был создан жуpнал движения по дебетуемому или кpедитуемому
 
 --внутpеннему счету или опеpационный день закpыт
 
 ------------------------------------------------------------------------------
 
CREATE TRIGGER kernel.BDTrnDays
   NO CASCADE BEFORE DELETE ON kernel.Trn
   REFERENCING OLD AS N FOR EACH ROW MODE DB2SQL
   WHEN (
      (SELECT Closed FROM kernel.Days A WHERE A.Dt=N.Dt)='Y'
   ) SIGNAL SQLSTATE '75004' ('Пеpед добавлением тpанзакции')
@

CREATE TRIGGER kernel.BDTrnDebet
   NO CASCADE BEFORE DELETE ON kernel.Trn
   REFERENCING OLD AS N FOR EACH ROW MODE DB2SQL
   WHEN ( 
      N.Dt <= (SELECT VALUE(MAX(Dt),DATE('01.01.0001')) FROM kernel.Jrn D
                       WHERE D.Stuff=N.Stuff AND D.Account=N.DAccount )
   ) SIGNAL SQLSTATE '75005' ('Существует журнал остатков по дебетуемому счету')
@

CREATE TRIGGER kernel.BDTrnCredit
   NO CASCADE BEFORE DELETE ON kernel.Trn
   REFERENCING OLD AS N FOR EACH ROW MODE DB2SQL
   WHEN ( 
      N.Dt <= (SELECT VALUE(MAX(Dt),DATE('01.01.0001')) FROM kernel.Jrn C
                       WHERE C.Stuff=N.Stuff AND C.Account=N.CAccount )
   ) SIGNAL SQLSTATE '75006' ('Существует журнал остатков по кредитуемому счету')
@