2

For a school assignment we need to keep the number of rows in a MySQL table under 100. The newest rows must stay. I've tried using a trigger but with no success.

CREATE TRIGGER delete_rows 
AFTER INSERT ON `check`.log 
FOR EACH ROW 
BEGIN 
   IF(SELECT COUNT(*) FROM `check`.log)>100 
   THEN 
      DELETE FROM `check`.log LIMIT 1; 
   END IF; 
END

This resulted in the following error:

ERROR 1442 (HY000): Can't update table 'log' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Then I tried monitoring /var/lib/mysql/check/log.ibd with incron and let it execute a script. When I open log.ibd with nano, incron executes my script, but when I use MySQL to insert something into it, nothing happens.

The incron instruction:

/var/lib/mysql/check/log.ibd IN_ALL_EVENTS /etc/scripts/test

How can I ensure that the table never has more than 100 rows?

1
  • Read error message carefully! It tells you CANNOT delete from the table which event the trigger is defined on. So trigger cannot help you - SP only, either insert-delete or rotation.
    – Akina
    Nov 20, 2018 at 15:10

2 Answers 2

0

Your trigger is not specifying which row to delete and is only limiting it to deleting a single record. You are going to want to update the trigger to specify which record to delete and I would guess that you want to delete the oldest one.

https://dev.mysql.com/doc/refman/5.6/en/delete.html

The LIMIT clause places a limit on the number of rows that can be deleted

0

Can triggers access tables?

A trigger can access both old and new data in its own table. A trigger can also affect other tables, but it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.


How can I ensure that the table never has more than 100 rows?

You can use the following routines to maintain the 100 rows (inserts the latest row and deletes the older one once the row count is 100) in the table t1.

CREATE TABLE t1 (
  c1 INT AUTO_INCREMENT PRIMARY KEY
);

Create a trigger which limits the number of rows to 100.

delimiter //
CREATE TRIGGER trigr_t1 BEFORE INSERT ON t1
  FOR EACH ROW
    BEGIN
      IF(SELECT COUNT(*) FROM `test_db`.t1) = 100 
      THEN 
        SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = 'An error has occurred - table t1 row count has reached 100. Use the procedure insert_t1 to insert a row.';
      END IF;
    END//
delimiter ;

Create a procedure which maintains 100 rows while inserting new row (and deleting the older row).

delimiter //
CREATE PROCEDURE insert_t1()
  BEGIN
   IF (SELECT COUNT(*) FROM `test_db`.t1) < 100 
   THEN 
      INSERT INTO `test_db`.t1 VALUES();
   ELSEIF (SELECT COUNT(*) FROM `test_db`.t1) = 100
   THEN
      DELETE FROM `test_db`.t1 LIMIT 1;
      INSERT INTO `test_db`.t1 VALUES();
   ELSE
     SIGNAL SQLSTATE '45000'
       SET MESSAGE_TEXT = 'An error has occurred - table row count is already > 100 !!!';
   END IF; 
  END//
delimiter ;

Now, you can insert rows into the table t1, initially:

INSERT INTO t1 VALUES();

Or, use the:

CALL insert_t1();

Once, the row count is 100, the trigger trigr_t1 will not allow any inserts using the INSERT INTO ... syntax. Then, you can use the procedure to insert new rows:

CALL insert_t1();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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