CODEWITHSUNDEEP
mysqltriggerssql-delete
viversba
viversba

Reputation: 11

Trigger for count number of deletes

I have this table called books

create table books (price INT,title VARCHAR(50),type VARCHAR(5));

And I need a trigger that inserts in another table called myregister the current date, and the number of books deleted in that date. So I came up with this:

    CREATE TABLE myregister (dates DATE,number INT);

    DELIMITER |
    CREATE TRIGGER deleted AFTER DELETE on books
    FOR EACH ROW BEGIN
    set @cnt = if(@cnt is null, 1, (@cnt+1));
    set @dat = CURDATE();
    INSERT INTO myregister values(CURDATE(),@cnt);
    END;
    |
    DELIMITER ;

but there are 2 problems:

  1. The variable is global, and it counts for every book deleted, it doesn't matter the date.

  2. The trigger counts for every row, so at the end, the table myregister has n rows, each one for every book deleted.

Upvotes: 1

Views: 1241

Answers (1)

Matt Raines
Matt Raines

Reputation: 4218

If you redefine the myregister table so that the dates column is unique, you could rewrite the trigger to insert a row if one does not already exist, or increment the counter if it does.

Caution: number is a reserved word in MySQL. Your trigger could have a more-specific name since deleted won't be obvious if you add more triggers for other tables.

CREATE TABLE myregister (dates DATE PRIMARY KEY, `number` INT);

CREATE TRIGGER deleted AFTER DELETE on books
FOR EACH ROW
    INSERT INTO myregister VALUES(CURDATE(), 1)
    ON DUPLICATE KEY UPDATE `number` = `number` + 1;

INSERT INTO books VALUES(1, 'test', 'test'), (2, 'test', 'test'),
   (3, 'test', 'test'), (4, 'test', 'test'), (5, 'test', 'test');

DELETE FROM books;

INSERT INTO books VALUES(1, 'test', 'test'), (2, 'test', 'test'),
   (3, 'test', 'test'), (4, 'test', 'test'), (5, 'test', 'test');

DELETE FROM books;

SELECT * FROM myregister;

-- 2016-05-09,  10

Upvotes: 1

Related Questions