Re: Trigger for read-only column

[Peter Brawley]

Without a Handler, SIGNAL under a SqlState error condition stops execution. Try the null-safe operator and define a warning SqlState instead ...

drop table if exists t;
create table t(id int, j int);
delimiter go
create trigger tidlock before update on t for each row 
begin 
  set @monitor='Starting';
  if !(new.id <=> old.id) then 
    signal sqlstate '01999' set message_text = 'ID may not be updated.'; 
    set new.ID=old.ID;
  end if; 
  set @monitor='Continuing';
end; 
go
delimiter ;
insert into t values(null,1);
update t set id=2,j=2 where id is null;
select id, j, @monitor from t;
+------+------+------------+
| id   | j    | @monitor   |
+------+------+------------+
| NULL |    2 | Continuing |
+------+------+------------+

Edited 1 time(s). Last edit at 06/15/2013 12:32PM by Peter Brawley.