CODEWITHSUNDEEP
mysqlsqltriggersinsertsql-delete
ditto
ditto

Reputation: 6287

MySQL: Trigger that'll increment a columns value, based on DELETE and INSERT

I'm wanting to setup a trigger that'll increment a columns value. If I do an INSERT I want it to + 1, and if I do a DELETE I want it to -1.

INSERT INTO `users saved` SET `userid` = '$user->id', `topicid` = '$topicid'
DELETE FROM `users saved` WHERE `userid` = '$user->id' AND `topicid` = '$topicid' LIMIT 1

DB Schema

`users saved`
id | userid | topicid

`total`
topicid | value

The table I want the trigger to effect is total, I want the value column (int) to go up or down.

I'm quite unsure where to start.

Upvotes: 0

Views: 1076

Answers (1)

Mauricio Trajano
Mauricio Trajano

Reputation: 2937

This should work although I did not test it, the statement for delete is very similar:

CREATE TRIGGER increment_value_on_insert 
    AFTER INSERT ON `users saved`
    REFERENCING
        NEW ROW as new_row
    FOR EACH ROW BEGIN
    UPDATE total
    SET value = value+1
    WHERE
     topicid = new_row.topicid
    END

Upvotes: 1

Related Questions