CODEWITHSUNDEEP
mysql
Sagar Mistry
Sagar Mistry

Reputation: 21

Do not let user update a column value in MYSQL

I m trying to basically not let anyone UPDATE a row value in MYSQL DB. I tried a simple logic using triggers. I created a trigger AFTER UPDATE in that particular table and passed an update query as an output. (Basically when someone updates that value trigger updates it back to the old one)

But looks like a failed attempt. After adding this trigger, when I try to update the value it flashes an error saying

#1442 - Can't update table 'wp_options' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

And sadly the value is updated and the trigger does not work.

How to prevent updation of that particular row?

Upvotes: 0

Views: 1261

Answers (1)

Akina
Akina

Reputation: 42612

I m trying to basically not let anyone UPDATE a row value in MYSQL db.

CREATE TRIGGER tr_bu_prevent_data_change
BEFORE UPDATE
ON table_name
FOR EACH ROW
BEGIN
    SET NEW.column1 = OLD.column1;
    -- another columns which must be non-changed
    SET NEW.columnN = OLD.columnN;
END

Apply when only some columns must be unchanged.

CREATE TRIGGER tr_bu_prevent_data_change
BEFORE UPDATE
ON table_name
FOR EACH ROW
SIGNAL SQLSTATE '45000'
       SET MESSAGE_TEXT = 'The data in \'table_name\' cannot be altered!', 
           MYSQL_ERRNO = 1105;

Apply when any data change attempt is errorneous.

Upvotes: 2

Related Questions