29 lines
958 B
PL/PgSQL
29 lines
958 B
PL/PgSQL
CREATE OR REPLACE FUNCTION trigger_service_events_notifications()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
-- Handle new Event insertions
|
|
IF TG_TABLE_NAME = 'Event' AND TG_OP = 'INSERT' THEN
|
|
INSERT INTO "Notification" ("userId", "type", "aboutServiceId", "aboutEventId")
|
|
SELECT np."userId", 'EVENT_CREATED', NEW."serviceId", NEW.id
|
|
FROM "_onEventCreatedForServices" oes
|
|
JOIN "NotificationPreferences" np ON oes."A" = np.id
|
|
WHERE oes."B" = NEW."serviceId"
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM "Notification" n
|
|
WHERE n."userId" = np."userId"
|
|
AND n."type" = 'EVENT_CREATED'
|
|
AND n."aboutEventId" = NEW.id
|
|
);
|
|
END IF;
|
|
|
|
RETURN NULL;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Trigger for new Events
|
|
DROP TRIGGER IF EXISTS eVENT_CREATED_notifications_trigger ON "Event";
|
|
CREATE TRIGGER eVENT_CREATED_notifications_trigger
|
|
AFTER INSERT ON "Event"
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION trigger_service_events_notifications();
|