/* Table: MOVETELEPHONES, Owner: SYSDBA */ 

CREATE TABLE  "MOVETELEPHONES"  
(
   "MOVEID" 	SMALLINT NOT NULL,
   "MNAKL" 	INTEGER NOT NULL,
   "MTEL" 	INTEGER NOT NULL,
   "MQUAN" 	INTEGER NOT NULL,
   "MPRICE" 	DOUBLE PRECISION,
 PRIMARY KEY ( "MOVEID" )
);
ALTER TABLE  "MOVETELEPHONES"  ADD CONSTRAINT  "FK_MOVETELEPHONES"  FOREIGN KEY ( "MNAKL" ) REFERENCES NAKLS ( "NAKLID" ) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE  "MOVETELEPHONES"  ADD CONSTRAINT  "FK_MOVETELEPHONES_TEL"  FOREIGN KEY ( "MTEL" ) REFERENCES TELEPHONES ( "TELEPHONEID" ) ON UPDATE CASCADE;
SET TERM ^ ;


 /* Triggers only will work for SQL triggers */ 

CREATE TRIGGER  "BEF_INS_MOVE"  FOR  "MOVETELEPHONES"  
ACTIVE BEFORE INSERT POSITION  0 
AS
begin
   IF (NEW.MOVEID IS null) then NEW.MOVEID = GEN_ID(gen_move, 1 );
end
 ^

CREATE TRIGGER  "BEF_INS_MPRICE_MOVET"  FOR  "MOVETELEPHONES"  
ACTIVE BEFORE INSERT POSITION  1 
AS
declare variable Coeff float;
declare variable Opt double precision;
declare variable Rozn double precision;
declare variable FirmN integer;
declare variable TelID integer;
begin
select NFirm from nakls
where Nakls.naklid=new.mnakl
into :FirmN;
select FCoeff from FIRMS
where firms.firmid=:FirmN
into :Coeff;
select TOPT, TROZN, TELEPHONEID FROM TELEPHONES
WHERE telephones.telephoneid=NEW.mtel
INTO :Opt, :Rozn, :TelID;
if (new.mquan>= 10 ) then
begin
update movetelephones
set new.mprice=:Opt*:Coeff;
end else begin
update movetelephones
set new.mprice=:Rozn*:Coeff;
end
end
 ^

CREATE TRIGGER  "BEF_DEL_MOVE"  FOR  "MOVETELEPHONES"  
ACTIVE BEFORE DELETE POSITION  0 
AS
declare variable TypeN Integer;
declare variable FirmN integer;
declare variable Coeff float;
declare variable FCngDELTA double precision;
declare variable FFinDELTA double precision;
begin
 select NTYPE, NFIRM FROM nakls
 WHERE NAKLS.naklid=OLD.mnakl
 INTO :TypeN, :FirmN;
 select FCNGDELTA, FFINDELTA FROM FIRMS
 WHERE firms.firmid=:FirmN
 into :FCNGDELTA, :FFINDELTA;
 IF (:TypeN IN ( 1 ,  4 ,  5 )) THEN
 begin
  Update telephones
  SET TQUAN=TQUAN-OLD.mquan
  where TELEPHONEID=OLD.mtel;
  IF(:TypeN= 4 ) then
   UPDATE firms
   SET FCngDELTA=:FCngDELTA+OLD.mquan*OLD.MPRICE*:Coeff
   WHERE FIRMID=:FirmN;
  ELSE
   UPDATE FIRMS
   set FFINDELTA=:FFINDELTA+OLD.mquan*OLD.MPRICE*:Coeff
   WHERE FIRMID=:FirmN;
 end ELSE begin
  update TELEPHONES
  SET TQUAN=TQUAN+OLD.mquan
  WHERE TELEPHONEID=OLD.mtel;
  if (:TypeN= 4 ) then
   UPDATE FIRMS
   SET FCngDELTA=:FCngDELTA-OLD.mquan*OLD.MPRICE*:Coeff
   where FIRMID=:FirmN;
  ELSE
   UPDATE FIRMS
   SET FFinDELTA=:FFINDELTA-OLD.mquan*OLD.MPRICE*:Coeff
   where FIRMID=:FirmN;
 END
end
 ^

COMMIT WORK ^
SET TERM ;^