I want to add a trigger to my SQL such that it increments/decrements the counter according to insert-CodePudding

The table that keeps track of records is RecordTable. It has two columns, RecordNumber of type INT and RecordType of type varchar(10). This table increments the value of RecordNumber each time a new row is added to it and decrements the value of RecordNumber each time a row is deleted from it.

However, when I do this, MySQL Workbench shows me two errors. Both of them say:- "missing ';' ".

CREATE TRIGGER RecordTable_Insert AFTER INSERT ON RecordTable
FOR EACH ROW 
    BEGIN
        UPDATE RecordTable r set r.RecordNumber = r.RecordNumber   1; //error here
    END; //error here

CodePudding user response：

MySQL has a restriction on updating other rows in the same table in a trigger according to the documentation:

A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

Trigger creation will be valid:

DELIMITER //

CREATE TRIGGER RecordTable_Insert AFTER INSERT ON RecordTable
FOR EACH ROW 
    BEGIN
        UPDATE RecordTable r set r.RecordNumber = r.RecordNumber   1; 
    END//
    
DELIMITER ;

But when you try to insert a record, you will get an error

Can't update table 'RecordTable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

DB fiddle

CodePudding user response：

You can make RecordNumber as an auto increment column, and adjust to the fact that when you delete records and later do inserts, some numbers will be missed.