2

i have 2 tables the first one is

Table KEYS

game keys
WOW AAAAAAA-AAAAAAA-AAAAA
WOW BBBBBBB-BBBBBBB-BBBBB
L4D2 AAAAAAA-AAAAAAA-AAAAA

TABLE inventory

game country language pieces
WOW USA EN 0
L4D2 USA EN 0

What i want to achieve is that column pieces on inventory gets updated by COUNT the game column of the keys table so at the end my inventory table should look like

game country language pieces
WOW USA EN 2
L4D2 USA EN 1

What i have tried

DELIMITER $$
CREATE TRIGGER 'inventario' AFTER INSERT ON `KEYS` FOR EACH ROW BEGIN
UPDATE inventory.pieces WITH SELECT COUNT(*) keys.game FROM keys WHERE keys.game = inventory.game
END $$;

i dont know if theres any other method to achieve this automatically after updating/inserting data on KEYS table

3
  • You should not store in the database anything that can be derived dynamically from what's already there, in your case the number of copies of a particular game.
    – mustaccio
    Apr 3 at 17:49
  • thank you for answering so quick so you suggest i should update that column using php instead of a trigger or a stored procedure? @mustaccio
    – Minioua
    Apr 3 at 17:53
  • No, that is not at all what I suggest. Where did you get that from?
    – mustaccio
    Apr 3 at 18:00

2 Answers 2

2

If you are saying that the row in inventory necessarily exists, the body of the Trigger is something like this:

FOR EACH ROW
    UPDATE inventory
        SET pieces = pieces + 1
        WHERE old.game = inventory.game

If you need to insert rows that have not yet been created:

FOR EACH ROW
    INSERT INTO inventory (
        ( game, country, language, pieces )
        VALUES
        ( OLD.game, 'USA', 'EN', 1 )
    ON DUPLICATE KEY UPDATE
        SET pieces = pieces + 1

(Something would need to be done if it is not USA and EN.)

1

i sincerly apologize i tried to search and search kind everywhere just after i posted i noticed on related questions this has been answered in the past https://dba.stackexchange.com/a/135296/250535 sorry for this useless thread

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.