MySQL Forums
Forum List  »  Triggers

Re: Trigger for read-only column
Posted by: Peter Brawley
Date: June 15, 2013 09:46AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
1595
June 12, 2013 09:25AM
Re: Trigger for read-only column
1503
June 15, 2013 09:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.