|
drop function if exists notify_update_status() cascade;
|
|
|
|
CREATE FUNCTION public.notify_update_status()
|
|
RETURNS trigger
|
|
LANGUAGE 'plpgsql'
|
|
COST 100
|
|
VOLATILE NOT LEAKPROOF
|
|
AS $BODY$
|
|
DECLARE
|
|
submodule_exists BOOLEAN;
|
|
userprofile_exists BOOLEAN;
|
|
notification_payload TEXT;
|
|
BEGIN
|
|
-- Comprobar si IdModule = 1
|
|
IF NEW."IdModule" <> 1 THEN
|
|
RAISE NOTICE 'IdModule no es 1';
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
-- Comprobar si el status ha cambiado a 4
|
|
IF NEW."Status" <> 4 OR OLD."Status" = 4 THEN
|
|
RAISE NOTICE 'El status de la evaluacion no ha cambiado a 4';
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
-- Comprobar si existe un registro en Company_Submodules con IdAccount = IdCompany y IdSubmodule = 13
|
|
SELECT EXISTS(SELECT 1
|
|
FROM "Company_Submodules"
|
|
WHERE "IdCompany" = NEW."IdAccount"
|
|
AND "IdSubmodule" = 13)
|
|
INTO submodule_exists;
|
|
|
|
IF NOT submodule_exists THEN
|
|
RAISE NOTICE 'No se encontró el registro en Company_Submodules con IdAccount = % y IdSubmodule = 13', NEW."IdAccount";
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
-- Comprobar si existe un registro en UserProfile con IdUser y TI_Requerido = true
|
|
SELECT EXISTS(SELECT 1
|
|
FROM "UserProfile" up
|
|
JOIN "AMPuestos" p on p."Id" = up."jobProfile"
|
|
JOIN "AMSRelations" r on p."Id" = r."idPuesto"
|
|
WHERE up."id" = NEW."IdUser"
|
|
AND up."TI_Requerido" = true
|
|
AND r."idSubmodulo" = 13)
|
|
INTO userprofile_exists;
|
|
|
|
IF NOT userprofile_exists THEN
|
|
RAISE NOTICE 'No se encontró el registro en UserProfile con IdUser = % y TI_Requerido = true', NEW."IdUser";
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
-- Construir la carga útil para la notificación
|
|
notification_payload :=
|
|
'{"Id": ' || NEW."Id"::text || ', "IdAccount": ' || NEW."IdAccount"::text ||
|
|
',' ||
|
|
' "IdUser": ' || NEW."IdUser"::text ||
|
|
'}';
|
|
PERFORM pg_notify('status_updated', notification_payload);
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$BODY$;
|
|
|
|
ALTER FUNCTION public.notify_update_status()
|
|
OWNER TO "MASV";
|
|
|
|
|
|
create trigger update_status_trigger
|
|
after update
|
|
on "TST_VLTNS"
|
|
for each row
|
|
execute function notify_update_status();
|
