mysql为什么说我的表不可更新,mysql表中的不可更新列

How to define a mysql table that should have one column as non updatable I mean once inserted it should not allow user to update in any way.

For Example table named tbl_xyz have 3 columns where one is non updatable then

tbl_xyz(c1,c2,c3), suppose I Insert values as (MBX,123,POQ) then lets say if c3 is non updatable then it should be always have value with first entry as POQ if some one tries to update then it should give some constrained column specific error.

If someone have did same table level defination with above mentioned restriction then please help and let me know please.

Thanks

解决方案

This can be done via a trigger in MySQL:

DELIMITER ;;

CREATE TRIGGER `trig_tbl_xyz_before_update`

BEFORE UPDATE ON `tbl_xyz` FOR EACH ROW

BEGIN

IF NEW.c3 != OLD.c3 THEN

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = 'tbl_xyz.c3 is not allowed to be updated, stop trying to update it.';

END IF;

END;;

DELIMITER ;

This will give the following response when trying to update tbl_xyz.c3 to a different value:

Error Code: 1644. tbl_xyz.c3 is not allowed to be updated, stop trying to update it.

I used the 45000 error code because it was suggested in the SIGNAL documentation.

To signal a generic SQLSTATE value, use '45000', which means “unhandled user-defined exception.”