UPDATE OR INSERT INTO TBPARAMETRO (TABELA, CONTROLE, CODIGO, DESCRICAO, VALOR, OBSERVACAO, GRUPO, DATAHORA, USUARIO_ID)
                           VALUES ('TBUSUARIO', 111, 'VINC_SUPERVISOR', 'VINCULAR USUARIO AO SUPERVISOR', '0', 'DIGITE O CODIGO DO SUPERVISOR OU VAZIO/ZERO PARA TODOS', 'COM', '2018-01-23 16:09:15', 293)
                         MATCHING (TABELA, CONTROLE);

COMMIT WORK;



SET TERM ^ ;

CREATE OR ALTER trigger ttbsupervisor_a01iu for tbsupervisor
active after insert or update position 1
AS
BEGIN
  IF(NEW.ID > 0) THEN
  BEGIN
     IF(OLD.USUARIO_ID IS DISTINCT FROM NEW.USUARIO_ID AND COALESCE(NEW.USUARIO_ID, 0) > 0) THEN
     BEGIN
        UPDATE TBCONTROLE_USUARIO U
        SET U.VALOR_EXT = CAST(NEW.ID AS INTEGER)

        WHERE U.ID = FN_PARAMETRO_V2('TBUSUARIO.VINC_SUPERVISOR',0,false,true)
        AND U.USUARIO_ID = NEW.USUARIO_ID;
     END
     IF(COALESCE(NEW.USUARIO_ID, 0) = 0) THEN
     BEGIN
        UPDATE TBCONTROLE_USUARIO U
        SET U.VALOR_EXT = 0

        WHERE U.ID = FN_PARAMETRO_V2('TBUSUARIO.VINC_SUPERVISOR',0,false,true)
        AND U.USUARIO_ID = OLD.USUARIO_ID;
     END
  END
END
^

SET TERM ; ^



SET TERM ^ ;

CREATE trigger ttbsupervisor_b03d for tbsupervisor
active before delete position 1
AS
BEGIN
  IF(OLD.ID > 0) THEN
  BEGIN
     IF(OLD.USUARIO_ID > 0) THEN
     BEGIN
        UPDATE TBCONTROLE_USUARIO U
        SET U.VALOR_EXT = 0

        WHERE U.ID = (SELECT FIRST 1 T.ID FROM TBCONTROLE_U T WHERE T.PARAMETRO = 'VINCULAR USUARIO AO SUPERVISOR')
        AND U.USUARIO_ID = OLD.USUARIO_ID;
     END
  END
END
^

SET TERM ; ^


SET TERM ^ ;

CREATE OR ALTER trigger ttbcontrole_usuario_b01iu for tbcontrole_usuario
active before insert or update position 1
AS
BEGIN

     IF(NEW.VALOR_EXT = 0 AND NEW.ID = fn_parametro_v2('TBUSUARIO.VINC_SUPERVISOR',0,false,true)) THEN
     BEGIN
        UPDATE TBSUPERVISOR U
        SET U.USUARIO_ID = NULL

        WHERE U.ID = OLD.VALOR_EXT
        AND U.USUARIO_ID = OLD.USUARIO_ID;
     END

     IF(OLD.VALOR_EXT IS DISTINCT FROM NEW.VALOR_EXT AND NEW.ID = fn_parametro_v2('TBUSUARIO.VINC_SUPERVISOR',0,false,true) AND NEW.VALOR_EXT <> 0) THEN
     BEGIN

        UPDATE TBSUPERVISOR U
        SET U.USUARIO_ID = NULL

        WHERE U.ID = OLD.VALOR_EXT
        AND U.USUARIO_ID = OLD.USUARIO_ID;

        UPDATE TBSUPERVISOR U
        SET U.USUARIO_ID = NEW.USUARIO_ID

        WHERE U.ID = NEW.VALOR_EXT;
     END
END
^

SET TERM ; ^



