MySql - trigger for keeping min number of rows

问题:

I want to undo a delete statement if it brings the number of rows below 2000:

CREATE TRIGGER Trig1
AFTER DELETE ON Tab1

FOR EACH ROW
IF (SELECT COUNT(*) FROM Table1) < 2000
   BEGIN
      INSERT INTO Table1 (select * from old);
   END;
END IF;

But it is not working. Can someone tell me what am I doing wrong, and how this should look?

回答1:

try,

INSERT INTO Table1
SELECT * 
FROM   old

point to remember

• the number of tables and data types must match on both tables since you are using *

回答2:

You can try this code:

DELIMITER $$
DROP TRIGGER IF EXISTS `Trig1`$$
CREATE TRIGGER `Trig1`
AFTER DELETE ON Table1

FOR EACH ROW
BEGIN
IF (SELECT COUNT(*) FROM Table1) < 2000 THEN
    INSERT INTO Table2(field1, field2, field3) VALUES(old.field1, old.field2, old.field3);
END IF;
END$$

DELIMITER ;

I have actually tested it, and it works on my end.

回答3:

You would fare better by creating a BEFORE DELETE trigger that raises an error (and thereby causes the statement to abort) if the operation would result in too few records:

DELIMITER ;;

CREATE TRIGGER foo BEFORE DELETE ON Table1 FOR EACH ROW
IF (SELECT COUNT(*) FROM Table1) <= 2000 THEN SIGNAL SQLSTATE '45000' SET
  MESSAGE_TEXT = 'Cannot delete when table would result in too few records';
END IF;;

DELIMITER ;

Note that SIGNAL was only introduced in MySQL 5.5. If using an earlier version, you must instead force MySQL to raise an exception by performing an erroneous operation; I often CALL a non-existent procedure with e.g. CALL raise_error;.