CREATE TABLE TBOPERACAO_DRE (
    ID INTEGER NOT NULL,
    OPERACAO VARCHAR(10),
    TRANSFERENCIA INTEGER DEFAULT 0,
    AJUSTE INTEGER DEFAULT 0,
    DESPERDICIO INTEGER DEFAULT 0,
    BENEFICIAMENTO INTEGER DEFAULT 0,
    OUTROS INTEGER DEFAULT 1,
    NF INTEGER DEFAULT 0,
    PRODUCAO_CONSUMO INTEGER DEFAULT 0);

ALTER TABLE TBOPERACAO_DRE
ADD CONSTRAINT PK_TBOPERACAO_DRE
PRIMARY KEY (ID);

CREATE SEQUENCE GTBOPERACAO_DRE;

create trigger ttboperacao_dre_b01i for tboperacao_dre
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtboperacao_dre,1);
end;

COMMENT ON TABLE TBOPERACAO_DRE IS 'classificar as operações de estoque para a dre';

CREATE OR ALTER trigger ttboperacao_b0i for tboperacao
active before insert position 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GTBOPERACAO,1);

  UPDATE OR INSERT INTO TBOPERACAO_DRE (OPERACAO) VALUES (new.codigo) MATCHING (OPERACAO);

END;

CREATE OR ALTER trigger ttboperacao_b0d_01 for tboperacao
active before delete position 0
AS
DECLARE VARIABLE p1 integer;
Begin

  delete from TBOPERACAO_DRE d where d.operacao = old.codigo;

  if (OLD.CODIGO = 'WPS' or
      OLD.CODIGO = 'WPE' or
      OLD.CODIGO = 'CFI' or
      OLD.CODIGO = 'INF' or
      OLD.CODIGO = 'INE') then
      BEGIN
      Exception "..." 'OPERAÇÕES "WPS,WPE,CFI,INF,INE" SÃO RESERVADAS E NÃO PODEM SER EXCLUÍDAS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ';
      END

    For Select
    (Select First 1 A.CONTROLE From TbEstoque_Transacao_Item A Where A.Operacao_Codigo = OLD.CODIGO)
    
    FROM RDB$DATABASE
    Into :p1
    Do
    begin
      if (:P1 > 0) Then Exception "..." 'OPERAÇÃO JÁ USADA NAS TRANSAÇÕES DE ESTOQUE. ID.'||:P1||' . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ';
    end

End;

