7

I want to protect existing dates in a date column from being overwritten. So disallow updates to the date column and only allow inserts if the existing field value is NULL (date column default is NULL). Are triggers the only way to accomplish this in MySQL? If so, would the trigger below work?

create trigger date_check
before insert, update on date
for each row
begin
if(date IS NOT NULL) then
 SIGNAL 'date already set'
end if ;
end ;

Background: I have a table with critical dates that was accidentally changed due to user error. I put some checks in the user interface to prevent this from happening again but want another layer of safety directly with the database if possible.

reflexiv
  • 1,693
  • 1
  • 21
  • 24

3 Answers3

5

Yes, in MySQL triggers are the only way to do this. MySQL does not support constraints.

Your trigger is not exactly right. First, you have update on date, but this should be update on <table name>. Second, you are checking the date value used for the update. Perhaps you mean:

create trigger date_check_update
before update on <the table name goes here>
for each row
begin
    if (old.date IS NOT NULL) then
        SIGNAL 'date already set'
    end if ;
end;

An insert trigger on this condition doesn't make sense.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for catching my late night careless mistakes! – reflexiv Sep 17 '13 at 14:32
  • One remark: with the above stated solution, once the date column is NOT NULL, the whole row cannot be updated. If you like to keep the other fields updateable and only "freeze" the date column (allowing reset to `NULL`), the conditional might be changed to if (old.date IS NOT NULL AND old.date <> new.date). – Giehl Man May 04 '20 at 21:34
  • As of today, is this still the only viable solution for MySQL? Also, how does one reconcile this solution with the general concept that triggers are bad? – Jay Bienvenu Sep 07 '22 at 21:42
4

If anyone like me stumble upon this thread and is getting syntax error, it's because "When you try to raise errors via SIGNAL you need to specify the SQLSTATE which is the error code and for the user defined generic error codes its 45000 along with the message text MESSAGE_TEXT"

So the SIGNAL line should look like this.

signal SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Your custom error message';

See this answer for more details.

https://stackoverflow.com/a/42827275/4164651

Community
  • 1
  • 1
Edmund Lee
  • 2,514
  • 20
  • 29
1

Just combining the above two answers, however, if you are writing triggers directly at the terminal, you'll have to change the delimiter before writing the trigger and then change it back once done.

delimiter $$

 create trigger date_check_update
 before update on <the table name goes here>
 for each row
 begin
     if (old.date IS NOT NULL) then
    signal SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Your custom error message';
  end if ;
end $$

delimiter ;
Groggu
  • 11
  • 1