Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2013
    Posts
    1

    Unanswered: Simple Trigger Implementation doesn't run

    Hi,

    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.

    Thanks!
    -TH
    Code:
    CREATE TRIGGER TRENDING
    AFTER INSERT ON LIKES
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    	DECLARE likeCount INT;
    	DECLARE trendTimestamp TIMESTAMP;
    	DECLARE postid INT;
    	
    	(SELECT  postid = post_id, likeCount = COUNT(*)
    	FROM LIKES
    	GROUP BY post_id);
    	
    	IF likeCount >= 5 THEN 
    		SET trendTimestamp = CURRENT TIMESTAMP;
    
    	UPDATE POST SET TRENDING_TIMESTAMP = trendTimestamp WHERE post_id = postid;
    END @

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    but db2 complains and does not run the command.
    What error message(s) did you received?
    Copy and paste the error message(s) which you received.

    What are DB2 version/release and platform OS?


    By the way,

    the following statement may include some errors.
    Code:
    	(SELECT  postid = post_id, likeCount = COUNT(*)
    	FROM LIKES
    	GROUP BY post_id);
    (1) (SELECT ...);
    No such syntax(statement).
    (2) "postid = post_id"
    No such syntax in SELECT statement.
    (3) "GROUP BY post_id" may generate multiple rows. They can't be assigned to scalar variable(s).

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What error message(s) did you received?
    Copy and paste the error message(s) which you received.

    What are DB2 version/release and platform OS?
    I want to know those information.
    Because, it would be difficult to go furher more concrete discussins, without the information.


    Quote Originally Posted by tonkuma View Post
    ...
    ...

    By the way,

    the following statement may include some errors.
    Code:
    	(SELECT  postid = post_id, likeCount = COUNT(*)
    	FROM LIKES
    	GROUP BY post_id);
    (1) (SELECT ...);
    No such syntax(statement).
    (2) "postid = post_id"
    No such syntax in SELECT statement.
    (3) "GROUP BY post_id" may generate multiple rows. They can't be assigned to scalar variable(s).
    If I assumed DB2 for Linux, UNIX, and Windows, I want to recommend you to sudy the following references.

    (1) and (2)
    Study SET variable statement.
    SET variable - IBM DB2 9.7 for Linux, UNIX, and Windows

    (3)
    You may want to study REFERENCING NEW AS correlation-name clause.
    CREATE TRIGGER - IBM DB2 9.7 for Linux, UNIX, and Windows
    Code:
    Syntax
    
    >>-CREATE--+------------+--TRIGGER--trigger-name---------------->
               '-OR REPLACE-'                          
    
         .-NO CASCADE-.                                
    >--+-+------------+--BEFORE-+--| trigger-event |---------------->
       +-AFTER------------------+                      
       '-INSTEAD OF-------------'                      
    
    >--ON--+-table-name-+------------------------------------------->
           '-view-name--'   
    
    >--+----------------------------------------------------------------+-->
       |              .-----------------------------------------------. |   
       |              V  (1)   (2)         .-AS-.                     | |   
       '-REFERENCING----------------+-OLD--+----+--correlation-name-+-+-'   
                                    |      .-AS-.                   |       
                                    +-NEW--+----+--correlation-name-+       
                                    |            .-AS-.             |       
                                    +-OLD TABLE--+----+--identifier-+       
                                    |            .-AS-.             |       
                                    '-NEW TABLE--+----+--identifier-'       
    
    >--+-FOR EACH ROW-------------+--| triggered-action |----------><
       |  (3)                     |                         
       '-------FOR EACH STATEMENT-'                         
    
    trigger-event
    
    ...

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •