Unanswered: Simple Trigger Implementation doesn't run
I've been looking into databases recently and it's been really exciting. I recently learned about triggers in databases, and while the concept is relatively simple to understand, the implementation has been a bit more troublesome. I've been implementing a mock social network as a means to understand db2 and it's functionality more often.
The trigger I wish to implement has two tables which are relevant:
Posts(postid, ...., trending_timestamp) and Likes(userid, postid). Every post can be liked by users, and this relation is stored in the Likes table.
I decided that my trigger implementation would be as follows. The moment a post has 5 likes, it should be considered a "trending" post, and it's attribute `trending_timestamp` would be updated with the time that the fifth person liked it.
I've tried implementing it below, but db2 complains and does not run the command. Can anyone help me work through this? I would really appreciate it.
CREATE TRIGGER TRENDING
AFTER INSERT ON LIKES
FOR EACH ROW MODE DB2SQL
DECLARE likeCount INT;
DECLARE trendTimestamp TIMESTAMP;
DECLARE postid INT;
(SELECT postid = post_id, likeCount = COUNT(*)
GROUP BY post_id);
IF likeCount >= 5 THEN
SET trendTimestamp = CURRENT TIMESTAMP;
UPDATE POST SET TRENDING_TIMESTAMP = trendTimestamp WHERE post_id = postid;
But, it might be better to include all statements into an UPDATE statement, regardless the platform OS of your DB2.
- Remove all DECLAREed variables.
- Put "SELECT ..." and "IF ..." logic into WHERE clause of the UPDATE statement.