CODEWITHSUNDEEP
mysqltriggerssql-update
Chax
Chax

Reputation: 1061

MySQL prevent data modification in one column with trigger

I am trying to prevent a column from beign modified in an update. I have a column named created_at which is a timestamp but i want to put a lock on it.

I tried to create a BEFORE UPDATE TRIGGER without success.

DROP TRIGGER IF EXISTS Users_creation_update_exception;
DELIMITER //
CREATE TRIGGER Users_creation_update_exception
BEFORE UPDATE
ON `Users` FOR EACH ROW

    IF NEW.created_by <> (SELECT created_by FROM `Users` WHERE id = NEW.id LIMIT 1) THEN
        SIGNAL SQLSTATE '45000'; // It says error here
    END IF;

END//
DELIMITER ;

I don't know if triggers are the right answer for this kind of problem. I have put on my FORIGN KEY a ON UPDATE NO ACTION. I would like a similar behavior.

I looked for information in the SIGNAL page of MySQL documentation. It is the same on MariaDB SIGNAL page.

I don't know what I'm doing wrong.

I'm on phpmyadmin through XAMPP with

Upvotes: 1

Views: 2230

Answers (1)

wchiquito
wchiquito

Reputation: 16569

Try:

MariaDB [_]> SELECT VERSION();
+----------------+
| VERSION()      |
+----------------+
| 10.2.7-MariaDB |
+----------------+
1 row in set (0.00 sec)

MariaDB [_]> DROP TABLE IF EXISTS `Users`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> CREATE TABLE IF NOT EXISTS `Users` (
    ->   `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   `created_by` DATETIME NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> DROP TRIGGER IF EXISTS `Users_creation_update_exception`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [_]> DELIMITER //

MariaDB [_]> CREATE TRIGGER `Users_creation_update_exception`
    -> BEFORE UPDATE ON `Users`
    -> FOR EACH ROW
    -> BEGIN
    ->   IF TRUE THEN
    ->     SIGNAL SQLSTATE '45000'
    ->       SET MESSAGE_TEXT = 'An error occurred';
    ->   END IF;
    -> END//
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> DELIMITER ;

MariaDB [_]> INSERT INTO `Users`
    -> VALUES (NULL, NOW());
Query OK, 1 row affected (0.00 sec)

MariaDB [_]> UPDATE `Users` SET `created_by` = NOW();
ERROR 1644 (45000): An error occurred

Upvotes: 3

Related Questions