CODEWITHSUNDEEP
phpmysqlsqltriggers
SAR
SAR

Reputation: 1845

Trigger calculation, on insert of one table effect on other table

IDEA:

Having a table of item, user, assign now if I assign one item to user which the record will be save on table of assign,

table_item:
ID------INT
NAME----TEXT
COUNT---INT

table_user:
ID-------INT
NAME-----TEXT

table_assing:
ID------INT
USER----INT (user id)
ITEM----INT (item_id)
COUNT---INT (this is for subtractions from the column of COUNT table of item)

Here I want to set trigger on inserting to table (table_assing) the value of column COUNT should subtract from column of COUNT table of table_item

This is possible on PHP that I can set to query on once action but it will take lots of code if it's possible on MySQL that will be much better and fast and effective with accuracy

Upvotes: 1

Views: 251

Answers (2)

mocanuga
mocanuga

Reputation: 131

Something like this should work.

DELIMITER $$ USE database_name$$ CREATE TRIGGER trigger_name AFTER INSERT ON table_asign FOR EACH ROW BEGIN UPDATE table_item SET count=count+NEW.count WHERE id=NEW.id; END;$$

The 'NEW.id' refers to the new row in the table 'table_asign'

Upvotes: 1

SAR
SAR

Reputation: 1845

simple trigger after insert on table table_assign

UPDATE table_item
     SET table_item.count = (table_item.count - NEW.table_assign.count)
   WHERE table_item.id = table_assign.item

Upvotes: 1

Related Questions