CODEWITHSUNDEEP
mysqltriggers
a.m
a.m

Reputation: 169

Insert data in multiple tables with one trigger

I am new to SQL and triggers, so my question might be an easy one, but after a lot of searching I am not sure how to do it.

So, I have one table with players. When a player is created, I want to copy the new data to 2 tables. Some of the fields go to Product, and some into Product Description.

The problem is I can't make it insert the info in the second table (product description) with the trigger. I read that I can't use multiple triggers for the same operation, so I need to do it with one.

Here is the code I've come so far:

BEGIN
INSERT INTO product (product_id, manufacturer_id)
  SELECT player.id,player.team_id
  FROM player WHERE NOT EXISTS (SELECT * FROM product 
  WHERE product.product_id = player.id);


INSERT INTO product_description (product_id, name, description)
  SELECT player.id,CONCAT_WS(' ',player.first_name,player.last_name),player.about
  FROM player WHERE NOT EXISTS (SELECT * FROM product 
  WHERE product.product_id = player.id);
END

Any help will be appreciated.

Thanks :)

Upvotes: 0

Views: 2614

Answers (2)

a.m
a.m

Reputation: 169

Thank you for the reply. Your code helped me notice an error in mine so I fixed it and now it works :) This is the right line:

FROM player WHERE NOT EXISTS (SELECT * FROM product_description WHERE product_description.product_id= player.id);

Thanks again.

Upvotes: 0

Maximiliano Hornes
Maximiliano Hornes

Reputation: 101

I understand that you want to do something like a cascade insert, so I write this code that works. The difference with your code, it's that you use END and I use COMMIT;

BEGIN;

    INSERT INTO t1 (t) VALUES ("Test");

    INSERT INTO t2 (id, t) (
        SELECT id, CONCAT(t, id)
        FROM t1
        WHERE NOT EXISTS (
            SELECT id
            FROM t2
            WHERE t1.id = t2.id
        )
    );

    INSERT INTO t3 (id, t) (
        SELECT id, CONCAT(t, id)
        FROM t2
        WHERE NOT EXISTS (
            SELECT id
            FROM t3
            WHERE t2.id = t3.id
        )
    );

COMMIT;

The three tables have the same structure: - id: INT(11) - t: VARCHAR

And the cascade inserts should insert only the non existing records.

Upvotes: 2

Related Questions