Prevent a record form being deleted or updated via the use of a Mysql trigger

Question

I am using Mysql and trying to create a trigger on a table that prevents a specific record from being deleted or updated, example I have a table demo

id    username    password
1     dames       t312llok
2     sauce       12ff1fff1
3     hynes       5656166oo9

I would like to prevent the record:

id    username    password
1     dames       t312llok

from being deleted or updated via the use of a trigger

http://stackoverflow.com/questions/5132356/how-to-reject-delete-statement-in-before-delete-triger-mysql — ethrbunny, Oct 01 '12 at 14:02

score 0 · Accepted Answer · answered May 03 '16 at 15:07

If you are using MySQL 5.5 or higher, this is simple.

DELIMITER $$
DROP TRIGGER IF EXISTS demo_bd $$
CREATE TRIGGER demo_bd BEFORE DELETE ON demo FOR EACH ROW
BEGIN
  IF OLD.id = 1 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'This magical user cannot be deleted';
  END IF;
END $$
DELIMITER ;

For the update, it's exactly the same code, with minor tweaks.

DELIMITER $$
DROP TRIGGER IF EXISTS demo_bu $$
CREATE TRIGGER demo_bu BEFORE UPDATE ON demo FOR EACH ROW
BEGIN
  IF OLD.id = 1 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'This magical user cannot be updated';
  END IF;
END $$
DELIMITER ;

Also... don't store passwords in the database.

Prevent a record form being deleted or updated via the use of a Mysql trigger

1 Answers1