CODEWITHSUNDEEP
mysqlsqltriggers
Saha
Saha

Reputation: 3

MySQL TRIGGER : Delete or Update from table 2 after delete from table 1

I have two table :

Table 1

Book's Name Publications
Poem A
Novel A
Math B

and Table 2

Publications Number of Books
A 2
B 1

Now, I want to write a trigger that after deleting from Table 1 and Number of books = 0 , delete that row from table 2 and if Number of books not null update the value of the Number of books.

for Example :

1.delete from Table 1 where Bookname=poem

and

2.delete from Table 1 where Bookname=Math

Then Table 2 would be change like This

Publications Number of Books
A 1

Upvotes: 0

Views: 301

Answers (1)

d0little
d0little

Reputation: 476

I don't have enough reputation to comment but the two commenters are correct, I would use a view instead of a table for Table 2. For example:

CREATE VIEW dbo.vwTable2
AS
    SELECT Publications, COUNT(*) BooksNumber
    FROM Table1
    GROUP BY Publications

If it has to be a trigger this should do the trick. It's a bit hacky but that's the price you gotta pay when you want to solve a problem one way. I wrote this in mssql though so might need a bit of converting to mysql as I'm not well versed in it.

CREATE TRIGGER dbo.trUpdateTable2 ON dbo.Table1
AFTER INSERT
    ,UPDATE
    ,DELETE
AS
BEGIN
    SET NOCOUNT ON;

    TRUNCATE TABLE dbo.Table2

    INSERT INTO dbo.Table2
    SELECT Publications, COUNT(*) BooksNumber
    FROM dbo.Table1
    GROUP BY Publications

END;

Upvotes: 1

Related Questions