29 lines
1.0 KiB
PL/PgSQL
29 lines
1.0 KiB
PL/PgSQL
CREATE OR REPLACE FUNCTION trigger_karma_notifications()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
-- Only create notification if the user has enabled karma notifications
|
|
-- and the karma change exceeds their threshold
|
|
INSERT INTO "Notification" ("userId", "type", "aboutKarmaTransactionId")
|
|
SELECT NEW."userId", 'KARMA_CHANGE', NEW.id
|
|
FROM "NotificationPreferences" np
|
|
WHERE np."userId" = NEW."userId"
|
|
AND ABS(NEW.points) >= COALESCE(np."karmaNotificationThreshold", 10)
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM "Notification" n
|
|
WHERE n."userId" = NEW."userId"
|
|
AND n."type" = 'KARMA_CHANGE'
|
|
AND n."aboutKarmaTransactionId" = NEW.id
|
|
);
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Drop the trigger if it exists to ensure a clean setup
|
|
DROP TRIGGER IF EXISTS karma_notifications_trigger ON "KarmaTransaction";
|
|
|
|
-- Create the trigger to fire after inserts
|
|
CREATE TRIGGER karma_notifications_trigger
|
|
AFTER INSERT ON "KarmaTransaction"
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION trigger_karma_notifications(); |