mysql before delete trigger reinsert record if deleted or updated

Question

I am trying to write a before delete trigger on mysql table here's is what I am trying to do exactly; I need a specific record to always be in the table so say for example the record is deleted or updated the trigger would simply reinsert the initial record.

EXAMPLE
Tablename: Users
Table Columns(3):ID,Username,Password
and I am trying to keep the following record from being deleted(Initial Record)
Username=Dames, Password=Password

However Im new to triggers and have been trying a thing or 3 but it really seems I need some help, I hope I made the question clear, and thanks alot.

Do you simply want to prevent the original record from being modified/deleted (take a look at [this question](http://stackoverflow.com/questions/10502298/how-to-set-a-mysql-row-to-read-only/)), or to allow that new record to exist whilst preserving the original record too? — eggyal, Aug 26 '12 at 17:16
O wow Great! So is there any way for the trigger to be written though — dames, Aug 26 '12 at 17:30
Ok id want it to be a case where it can be updated however if it's updated then the initial record will be reinserted. — dames, Aug 26 '12 at 17:53

score 3 · Accepted Answer · answered Aug 26 '12 at 18:08

If you're happy to hard-code into the triggers the details of the record that you wish to preserve, then you could do something like:

DELIMITER ;;

CREATE TRIGGER foo_upd BEFORE UPDATE ON Users FOR EACH ROW
IF OLD.Username <=> 'Dames' AND OLD.Password <=> 'Password' THEN
  INSERT INTO Users (Username, Password) VALUES ('Dames', 'Password');
END IF ;;

CREATE TRIGGER foo_del BEFORE DELETE ON Users FOR EACH ROW
IF OLD.Username <=> 'Dames' AND OLD.Password <=> 'Password' THEN
  INSERT INTO Users (Username, Password) VALUES ('Dames', 'Password');
END IF ;;

DELIMITER ;

Otherwise, I'd recommend adding an additional boolean column to the Users table—e.g. locked:

ALTER TABLE Users ADD COLUMN locked BOOLEAN NOT NULL DEFAULT FALSE;

Then setting the value of that column to be TRUE on the record(s) that you wish to preserve:

UPDATE Users SET locked = TRUE
WHERE  Username = 'Dames' AND Password = 'Password';

Then creating triggers along the following lines:

DELIMITER ;;

CREATE TRIGGER foo_upd BEFORE UPDATE ON Users FOR EACH ROW
IF OLD.locked THEN
  INSERT INTO Users (Username, Password) VALUES (OLD.Username, OLD.Password);
END IF ;;

CREATE TRIGGER foo_del BEFORE DELETE ON Users FOR EACH ROW
IF OLD.locked THEN
  INSERT INTO Users (Username, Password) VALUES (OLD.Username, OLD.Password);
END IF ;;

DELIMITER ;

mysql before delete trigger reinsert record if deleted or updated

1 Answers1