CREATE TABLE tblValuta (
codValuta varchar( 3 ) NOT NULL PRIMARY KEY CHECK (LENGTH(TRIM(codValuta)) =  3 ),
keyValuta int NOT NULL, 
nameValuta varchar( 150 ) NOT NULL,
isActive bit NOT NULL DEFAULT  0 ,
sTerr long varchar NULL,
ts timestamp DEFAULT timestamp
)
;
CREATE MATERIALIZED VIEW vCurValuta
as
	SELECT codValuta, keyValuta, nameValuta, ts
	FROM tblValuta
	WHERE isActive =  1 
;
CREATE TABLE Cources (
	dDate date NOT NULL,
	codValuta char( 3 ) NOT NULL REFERENCES tblValuta(codValuta) ON UPDATE CASCADE,
	courceCB decimal ( 5 ,  4 ) NOT NULL,
	courceVB decimal ( 5 ,  4 )	NULL,
	ts timestamp NOT NULL DEFAULT timestamp,
	last_user varchar( 50 ) NOT NULL DEFAULT last user,
	PRIMARY KEY CLUSTERED (dDate, codValuta)
)
;

CREATE FUNCTION "fnGetSumInVal" (IN datedoc date, IN sumdoc decimal( 25 ,  4 ),
			IN val char( 3 ), IN request_val char( 3 ))
RETURNS decimal( 25 ,  4 ) -- ON EXCEPTION RESUME
DETERMINISTIC
BEGIN
	DECLARE REQUESTSUM DECIMAL( 25 ,  4 );
	DECLARE curse decimal( 25 ,  4 );
	
	IF VAL IS NULL OR sumdoc IS NULL OR DATEDOC	IS NULL OR request_val IS NULL
		OR request_val NOT IN (SELECT codValuta FROM tblValuta) 
		-- OR datedoc NOT IN (SELECT DISTINCT dDate FROM Cources)	--server crash
		-- OR VAL NOT IN (SELECT codValuta FROM Cources WHERE dDate = datedoc) --server crash
		THEN
		RETURN REQUESTSUM;
	END IF;

	SELECT CASE val 
		WHEN 'RUR' THEN  1 
		ELSE (SELECT courseCB FROM sa.Cources 
			WHERE dDate = datedoc AND codValuta = val )
	END INTO curse FROM DUMMY;
	
	SELECT CASE request_val
		WHEN 'RUR' THEN  1 
		ELSE (SELECT courseCB FROM sa.Cources 
			WHERE dDate = datedoc AND codValuta = request_val)
	END INTO REQUESTSUM FROM DUMMY;
	
	RETURN sumdoc * curse / REQUESTSUM;
END

ALTER FUNCTION "fnGetSumInVal" (IN datedoc date, IN sumdoc decimal( 25 ,  4 ),
			IN val char( 3 ), IN request_val char( 3 ))
RETURNS decimal( 25 ,  4 ) -- ON EXCEPTION RESUME
DETERMINISTIC
BEGIN
	DECLARE REQUESTSUM DECIMAL( 25 ,  4 );
	DECLARE curse decimal( 25 ,  4 );
	
	IF VAL IS NULL OR sumdoc IS NULL OR DATEDOC	IS NULL OR request_val IS NULL
		OR request_val NOT IN (SELECT codValuta FROM tblValuta) THEN
		RETURN REQUESTSUM;
	END IF;
	
	IF datedoc NOT IN (SELECT DISTINCT dDate FROM Cources) THEN
		RETURN REQUESTSUM;
	END IF;
	
	IF VAL NOT IN (SELECT codValuta FROM Cources WHERE dDate = datedoc) THEN
		RETURN REQUESTSUM;
	END IF;
	--SUM*CURS(val)/CURS(request_val)
	SELECT CASE val 
		WHEN 'RUR' THEN  1 
		ELSE (SELECT courseCB FROM sa.Cources 
			WHERE dDate = datedoc AND codValuta = val )
	END INTO curse FROM DUMMY;
	
	SELECT CASE request_val
		WHEN 'RUR' THEN  1 
		ELSE (SELECT courseCB FROM sa.Cources 
			WHERE dDate = datedoc AND codValuta = request_val)
	END INTO REQUESTSUM FROM DUMMY;
	
	RETURN sumdoc * curse / REQUESTSUM;
END

create table tblMain
(
	idMain bigint NOT NULL PRIMARY KEY DEFAULT GLOBAL AUTOINCREMENT,
	datedoc date NOT NULL,
	sumdoc decimal ( 15 ,  4 ) NOT NULL,
	codValuta char( 3 ) NOT NULL ,
.
.
.
	ts timestamp NOT NULL default timestamp,
	last_user varchar( 50 ) NOT NULL default last user,
	FOREIGN KEY (codValuta) REFERENCES tblValuta(codValuta) ON UPDATE CASCADE
) ;

select idMain,  
	fnGetSumInVal(datedoc,sumdoc,codValuta,'USD') as IN_USD,
	fnGetSumInVal(datedoc,sumdoc,codValuta,'EUR') as IN_EUR, 
	fnGetSumInVal(datedoc,sumdoc,codValuta,'RUR') as IN_RUR
	from sa.tblMain

CREATE TRIGGER "MySuperTrigger" 
BEFORE DELETE
ON table1
REFERENCING OLD AS DELETED NEW AS new_dept
FOR EACH ROW
WHEN ( DELETED.fielld1 !=  4  AND DELETED.field2 =  0 )
BEGIN
   SET new_dept.anyfield =  4 ;
END