CODEWITHSUNDEEP
mysqltriggers
a.4j4vv1
a.4j4vv1

Reputation: 121

how to pass parameter to trigger in mysql

i need to add one number to post.like_count where post.id = like.module_id ! like.module_id definition when insert into the like table

(post table) id user_id like_count

(like tabel) id module_id like

(like table trigger)

DELIMITER $$CREATE TRIGGER update_LikeCount 
AFTER INSERT ON like FOR EACH ROW  
BEGIN      
    UPDATE post SET hpost.like_count = post.like_count + 1       
    WHERE post.id = like.module_id;  
END 
$$DELIMITER ;

Upvotes: 1

Views: 8566

Answers (2)

RandomSeed
RandomSeed

Reputation: 29769

You can refer to the fields of the row you are about to insert with the NEW keyword (just as you would use the table name):

CREATE TRIGGER ...
UPDATE post SET hpost.like_count = post.like_count + 1       
WHERE post.id = NEW.module_id; 
...

Notice the OLD keyword is also available to ON UPDATE and ON DELETE triggers.

Just a side note regarding the relevance of this trigger at all. You seem to be trying to pre-calculate the number of "likes" for a given post. Unless your tables get really, really large and your database really, really busy, a query like this should be virtually instant, and gives you the guarantee to always return accurate, up-to-date figures, without the need to implement a complicated maintenance:

SELECT COUNT(module_id) FROM likes WHERE module_id = [the post id] ;

You might want to reconsider your approach.

Upvotes: 0

ScottGuymer
ScottGuymer

Reputation: 1905

Without using any nasty little hacks you cant really pass parameters to triggers (they are independent of the code that triggered them which could come from anywhere (app code, management console etc))

Looks to me like you might want to change your architecture a little to incorporate the user id into the like table...

(i might not have understood the scenario correctly so please update with more info if you think it will help!)

Upvotes: 2

Related Questions