CREATE OR ALTER TRIGGER TTBPEDIDO_ITEM_B01UD FOR TBPEDIDO_ITEM
ACTIVE BEFORE UPDATE OR DELETE POSITION 0
AS
DECLARE VARIABLE CONFERENCIA_CODIGO VARCHAR(500);
DECLARE VARIABLE QTD_TOTAL_CONFERENCIA NUMERIC(18,4);
BEGIN
        IF(UPDATING) THEN
        BEGIN
           IF((OLD.PRODUTO_CODIGO <> NEW.PRODUTO_CODIGO)
           OR (OLD.TAMANHO <> NEW.TAMANHO)
           OR (OLD.QUANTIDADE <> NEW.QUANTIDADE)
           OR (OLD.QTD_EST IS DISTINCT FROM NEW.QTD_EST AND NEW.QTD_EST < OLD.QTD_EST AND NEW.QTD_EST = 0 AND NEW.SITUACAO <> 2)
           OR (OLD.QTD_AGP IS DISTINCT FROM NEW.QTD_AGP AND NEW.QTD_AGP < OLD.QTD_AGP AND NEW.QTD_AGP = 0 AND NEW.SITUACAO <> 2)) THEN
           BEGIN

               FOR SELECT
                    COALESCE(LIST(FN_LPAD(B.TABELA_ID, 8, '0')||B.SEQUENCIA, ', '), ''),
                    COALESCE(SUM(A.QTD_TOTAL), 0)
                FROM TBCONFERENCIA_ITEM A, TBCONFERENCIA B
                WHERE A.CONFERENCIA_ID = B.ID
                AND B.TABELA = 'PED'
                AND B.TABELA_ID = OLD.PEDIDO
                AND B.STATUS >= 1
                AND A.TABELA_ITEM_ID = OLD.CONTROLE
                INTO :CONFERENCIA_CODIGO, :QTD_TOTAL_CONFERENCIA DO
                BEGIN
                END
    
                IF (OLD.TAMANHO <> NEW.TAMANHO AND :CONFERENCIA_CODIGO <> '') THEN
                BEGIN
                    EXCEPTION "..." 'ITEM: '||FN_LPAD(OLD.SEQUENCIA, 3, '0')||' JA VINCULADO NA(S) CONFERENCIA(S): '||:CONFERENCIA_CODIGO||'. OPERACAO CANCELADA!';
                END
    
                IF (OLD.QUANTIDADE <> NEW.QUANTIDADE AND :CONFERENCIA_CODIGO <> '') THEN
                BEGIN
                    IF (NEW.QUANTIDADE < :QTD_TOTAL_CONFERENCIA) THEN
                    BEGIN
                        EXCEPTION "..." 'ITEM: '||FN_LPAD(OLD.SEQUENCIA, 3, '0')||' JA VINCULADO NA(S) CONFERENCIA(S): '||:CONFERENCIA_CODIGO||'. OPERACAO CANCELADA!';
                    END
                END
    
                IF (OLD.QTD_EST IS DISTINCT FROM NEW.QTD_EST AND NEW.QTD_EST < OLD.QTD_EST AND NEW.QTD_EST = 0 AND NEW.SITUACAO <> 2 AND :CONFERENCIA_CODIGO <> '') THEN
                BEGIN
                    EXCEPTION "..." 'ITEM: '||FN_LPAD(OLD.SEQUENCIA, 3, '0')||' JA VINCULADO NA(S) CONFERENCIA(S): '||:CONFERENCIA_CODIGO||'.<br> OPERACAO DE DESLOCACAO CANCELADA!';
    
                END
    
                IF (OLD.QTD_AGP IS DISTINCT FROM NEW.QTD_AGP AND NEW.QTD_AGP < OLD.QTD_AGP AND NEW.QTD_AGP = 0 AND NEW.SITUACAO <> 2 AND :CONFERENCIA_CODIGO <> '') THEN
                BEGIN
                    EXCEPTION "..." 'ITEM: '||FN_LPAD(OLD.SEQUENCIA, 3, '0')||' JA VINCULADO NA(S) CONFERENCIA(S): '||:CONFERENCIA_CODIGO||'.<br> OPERACAO DE DESLOCACAO CANCELADA!';
                END
            END
        END

        IF(DELETING)THEN
        BEGIN
              SELECT
                    COALESCE(LIST(FN_LPAD(B.TABELA_ID, 8, '0')||B.SEQUENCIA, ', '), ''),
                    COALESCE(SUM(A.QTD_TOTAL), 0)
                FROM TBCONFERENCIA_ITEM A, TBCONFERENCIA B
                WHERE A.CONFERENCIA_ID = B.ID
                AND B.TABELA = 'PED'
                AND B.TABELA_ID = OLD.PEDIDO
                AND B.STATUS >= 1
                AND A.TABELA_ITEM_ID = OLD.CONTROLE
                INTO :CONFERENCIA_CODIGO, :QTD_TOTAL_CONFERENCIA;

                IF (:CONFERENCIA_CODIGO <> '') THEN
                BEGIN
                    EXCEPTION "..." 'ITEM: '||FN_LPAD(OLD.SEQUENCIA, 3, '0')||' JA VINCULADO NA(S) CONFERENCIA(S): '||:CONFERENCIA_CODIGO||'. OPERACAO CANCELADA!';
                END        
        END
   
END;