SET TERM ^ ;

CREATE OR ALTER trigger ttbparametro_b0iud for tbparametro
active before insert or update or delete position 0
AS
declare variable TABELA VARCHAR(50);
declare variable sql blob sub_type 0 segment size 80;
begin
    
    TABELA =
        CASE IIF(DELETING,OLD.TABELA,NEW.TABELA)
            WHEN 'SISTEMA'    THEN 'TBCONTROLE_N'
            WHEN 'TBOPERADOR' THEN 'TBCONTROLE_O'
            WHEN 'TBUSUARIO'  THEN 'TBCONTROLE_U' 
            WHEN 'TBMODELO'   THEN 'TBCONTROLE_M'
        END;

    IF ( TABELA IS NOT NULL AND COALESCE(FN_GET_CONTEXT_TEMPORARY('ALTER_TBPARAMETRO'),0) <>  2 ) THEN
    BEGIN
        FN_SET_CONTEXT('ALTER_TBPARAMETRO',1);

        IF ( DELETING ) THEN
        BEGIN
            EXECUTE STATEMENT 'DELETE FROM ' || TABELA || ' WHERE ID = ' || OLD.CONTROLE || ';';
        END
        ELSE
        BEGIN
            sql = coalesce('
            UPDATE OR INSERT INTO ' || TABELA || ' (
                ID,
                GRUPO,
                PARAMETRO,
                VALOR_EXT,
                COMENTARIO
            ) VALUES (
                '   || coalesce(NEW.CONTROLE,0)    || ',
                ''' || coalesce(NEW.GRUPO,'')      || ''',
                ''' || coalesce(NEW.DESCRICAO,'')  || ''',
                ''' || coalesce(NEW.VALOR,'')      || ''',
                ''' || coalesce(NEW.OBSERVACAO,'') || '''
            ) MATCHING ( ID ); ','');

            EXECUTE STATEMENT sql;
        END
    END

end
^

SET TERM ; ^



