1-- message: Creating document position calulation tables ; 2 3CREATE TABLE DocCalcTime ( 4 TCalcID INTEGER PRIMARY KEY ASC autoincrement, 5 TemplID INT NOT NULL, 6 7 name VARCHAR(255), 8 minutes INT default 0, 9 percent INT default 0, 10 stdHourSet INT default 0, 11 allowGlobal INT default 1, 12 13 modDate TIMESTAMP(14) 14); 15CREATE INDEX calcTimeTemplIndx_2 ON DocCalcTime( TemplID ); 16CREATE TRIGGER update_docCalcTime_modDate AFTER UPDATE ON DocCalcTime 17BEGIN 18 UPDATE DocCalcTime SET modDate = DATETIME('NOW') WHERE TCalcID = new.TCalcID; 19END; 20 21 22CREATE TABLE DocCalcFixed( 23 FCalcID INTEGER PRIMARY KEY ASC autoincrement, 24 TemplID INT NOT NULL, 25 26 name VARCHAR(255), 27 amount DECIMAL(10,2) default 1.0, 28 price DECIMAL(10,2), 29 percent INT default 0, 30 modDate TIMESTAMP(14) 31); 32CREATE INDEX CalcFixedTemplIndx_2 ON DocCalcFixed( TemplID ); 33CREATE TRIGGER update_docCalcFixed_modDate AFTER UPDATE ON DocCalcFixed 34BEGIN 35 UPDATE DocCalcFixed SET modDate = DATETIME('NOW') WHERE FCalcID = new.FCalcID; 36END; 37 38 39CREATE TABLE DocCalcMaterials( 40 MCalcID INTEGER PRIMARY KEY ASC autoincrement, 41 TemplID INT NOT NULL, 42 name VARCHAR(255), 43 percent INT default 0, 44 modDate TIMESTAMP(14) 45); 46CREATE INDEX CalcMaterialTemplIndx_2 ON DocCalcMaterials( TemplID ); 47CREATE TRIGGER update_docCalcMaterials_modDate AFTER UPDATE ON DocCalcMaterials 48BEGIN 49 UPDATE DocCalcMaterials SET modDate = DATETIME('NOW') WHERE MCalcID = new.MCalcID; 50END; 51 52CREATE TABLE DocCalcMaterialDetails( 53 MCalcDetailID INTEGER PRIMARY KEY ASC autoincrement, 54 CalcID INT NOT NULL, 55 56 materialID INT NOT NULL, 57 amount DECIMAL(10,2) 58); 59CREATE INDEX CalcMaterialDetailsCalcIDIndx_2 ON DocCalcMaterialDetails( CalcID ); 60 61