Last active
September 28, 2022 10:42
-
-
Save Lyokolux/0aac5fe21effa274cc20747813ccc38f to your computer and use it in GitHub Desktop.
Masterarbeit - Notification Triggers
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- VOTE NOTIFICATIONS -- | |
| -- Notify the author of the participation that a vote is given -- | |
| CREATE TRIGGER IF NOT EXISTS VOTE_INSERT_NOTIFICATION | |
| AFTER INSERT ON Vote | |
| FOR EACH ROW | |
| BEGIN | |
| INSERT INTO Notification(notificationTypeId, content, toUserId) | |
| SELECT 'vote', Participation.id, Participation.authorId | |
| FROM Participation | |
| WHERE Participation.id == NEW.participationId; | |
| END; | |
| CREATE TRIGGER IF NOT EXISTS VOTE_UPDATE_NOTIFICATION | |
| AFTER UPDATE ON Vote | |
| FOR EACH ROW | |
| BEGIN | |
| DELETE FROM Notification WHERE notificationTypeId == 'vote' AND content == OLD.participationId AND toUserId == ( | |
| SELECT authorId | |
| FROM Participation | |
| WHERE Participation.id == OLD.participationId | |
| ); | |
| INSERT INTO Notification(notificationTypeId, content, toUserId) | |
| SELECT 'vote', Participation.id, Participation.authorId | |
| FROM Participation | |
| WHERE Participation.id == NEW.participationId; | |
| END; | |
| -- submissionComment NOTIFICATIONS -- | |
| -- Notify the author of the participation that a comment is posted on it -- | |
| CREATE TRIGGER IF NOT EXISTS PARTICIPATION_COMMENT_NOTIFICATION | |
| AFTER INSERT ON Comment | |
| FOR EACH ROW | |
| BEGIN | |
| INSERT INTO Notification(notificationTypeId, content, toUserId) | |
| SELECT 'submissionComment', Participation.id, Participation.authorId | |
| FROM Participation | |
| -- INSERT if it is on participation and is not a reply -- | |
| WHERE NEW.onParticipationId == Participation.id AND NEW.parentId IS NULL; | |
| END; | |
| -- replyComment NOTIFICATIONS -- | |
| -- Notify the author of a comment that a reply is posted on it -- | |
| CREATE TRIGGER IF NOT EXISTS REPLY_COMMENT_NOTIFICATION | |
| AFTER INSERT ON Comment | |
| FOR EACH ROW | |
| BEGIN | |
| INSERT INTO Notification(notificationTypeId, content, toUserId) | |
| SELECT 'replyComment', User.name, Comment.authorId | |
| FROM Comment, User | |
| -- INSERT if it is on participation and is not a reply -- | |
| WHERE Comment.id == NEW.parentId AND User.email == NEW.authorId ; | |
| END; | |
| -- Congratulation NOTIFICATIONS -- | |
| -- Notify the recipient of a congratulation -- | |
| CREATE TRIGGER IF NOT EXISTS CONGRATULATION_NOTIFICATION | |
| AFTER INSERT ON Congratulation | |
| FOR EACH ROW | |
| BEGIN | |
| INSERT INTO Notification(notificationTypeId, content, toUserId) | |
| VALUES ('congratulation', NEW.fromUser || '-' || NEW.participationId, NEW.toUser); | |
| END; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment