CREATE OR REPLACE FUNCTION atualizarNome()
    RETURNS trigger AS
    $BODY$
    /******************************************************************************
      NAME: atualizarNome
      DESCRIPTION: Trigger disparada sempre que o Nome Social ou Nome Real for
      alterado atualizando o campo Nome.

      ticket #45953

      REVISIONS:
      Ver       Date       Author                Description
      --------- ---------- ------------------    ---------------------------------
      1.0       17/02/2016   Jonas G. Diel         1. Trigger criada.
    ******************************************************************************/
    BEGIN
        IF TG_OP = 'UPDATE'
        THEN
            --Caso atualizar campo nome altera nome real
            IF NEW.name IS NOT NULL AND NEW.name != OLD.name
            THEN
                NEW.realname := NEW.name;
            END IF;
        ELSE
            --Caso passado nome salva campo nome real
            IF NEW.name IS NOT NULL AND NEW.name IS DISTINCT FROM NEW.socialname
            THEN
                NEW.realname := NEW.name;
            END IF;

            --Caso passado nome real atualiza campo nome
            IF NEW.realname IS NOT NULL
            THEN
                NEW.name := NEW.realname;
            END IF;
        END IF;

        --Caso passado nome social atualiza campo nome
        IF NEW.socialname IS NOT NULL
        THEN
            NEW.name := NEW.socialname;
        END IF;
        RETURN NEW;

    END;
    $BODY$
LANGUAGE plpgsql VOLATILE;

DROP TRIGGER IF EXISTS trg_atualizarNome ON basperson;
CREATE TRIGGER trg_atualizarNome
BEFORE INSERT OR UPDATE OF socialname, realname, name
ON basperson
FOR EACH ROW
EXECUTE PROCEDURE atualizarNome();

