dump vendas

[thiagogomesverissimo]

CREATE TABLE VENDAS (
  ID DMID NOT NULL,
  IDASSOCIADO DMID NOT NULL,
  IDCONVENIADO DMID NOT NULL,
  DATA DMDATA,
  VALOR DMNUMERICO,
  QTDPARCELAS INTEGER NOT NULL,
  DESCRICAO VARCHAR(1000) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  USUARIO DMUSUARIO,
  DHINCLUSAO DMDHINCLUSAO,
  DHMODIFICACAO DMDHMODIFICACAO,
  "CHECK" DMBOLEANO);


ALTER TABLE VENDAS ADD CONSTRAINT PK_VENDAS PRIMARY KEY (ID);

ALTER TABLE VENDAS ADD CONSTRAINT FK_VENDASASSOCIADOS FOREIGN KEY (IDASSOCIADO) REFERENCES ASSOCIADOS(ID) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE VENDAS ADD CONSTRAINT FK_VENDASCONVENIADOS FOREIGN KEY (IDCONVENIADO) REFERENCES CONVENIADOS(ID) ON DELETE CASCADE ON UPDATE CASCADE;


SET TERM ^ ;

CREATE TRIGGER VENDAS_BI FOR VENDAS
ACTIVE BEFORE INSERT
POSITION 1
AS
BEGIN
  IF (NOT EXISTS(SELECT ID FROM VENDAS WHERE ID = NEW.ID
  AND IDASSOCIADO = NEW.IDASSOCIADO AND IDCONVENIADO = NEW.IDCONVENIADO
  AND "CHECK" = 'N')) THEN
  BEGIN
    IF (NEW.ID IS NULL) THEN
      NEW.ID = GEN_ID(VENDAS_ID_GEN, 1);
    NEW.DHINCLUSAO = 'NOW';
    NEW.DHMODIFICACAO = 'NOW';
    NEW."CHECK" = 'N';
  END
  ELSE
    EXCEPTION REGISTROJAEXISTE;
END^

CREATE TRIGGER VENDAS_BU FOR VENDAS
ACTIVE BEFORE UPDATE
POSITION 1
AS
BEGIN
  NEW.DHMODIFICACAO = 'NOW';
END^
SET TERM ; ^