Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Search | Email

5.2. 保護表欄位

通過觸發器,使之無法修改某些欄位的數據,同時不影響修改其他欄位。

DROP TRIGGER IF EXISTS `members`;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE TRIGGER `members` BEFORE UPDATE ON `members` FOR EACH ROW BEGIN
	set new.name 		= old.name;
	set new.cellphone 	= old.cellphone;
	set new.email 		= old.email;
    set new.password 	= old.password;
END//
DELIMITER ;
SET SQL_MODE=@OLD_SQL_MODE;
		

再舉一個例子

CREATE TABLE `account` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`user` VARCHAR(50) NOT NULL DEFAULT '0',
	`cash` FLOAT NOT NULL DEFAULT '0',
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
		

每一次數據變化新增一條數據

INSERT INTO `test`.`account` (`user`, `cash`) VALUES ('neo', -10);
INSERT INTO `test`.`account` (`user`, `cash`) VALUES ('neo', -5);
INSERT INTO `test`.`account` (`user`, `cash`) VALUES ('neo', 30);
INSERT INTO `test`.`account` (`user`, `cash`) VALUES ('neo', -20);
		

保護用戶的餘額不被修改

DROP TRIGGER IF EXISTS `account`;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE TRIGGER `account` BEFORE UPDATE ON `account` FOR EACH ROW BEGIN
	set new.cash 		= old.cash;
END//
DELIMITER ;
SET SQL_MODE=@OLD_SQL_MODE;