MySQL 觸發(fā)器
MySQL 觸發(fā)器的語(yǔ)法:
CREATE TRIGGER <觸發(fā)器名稱> <--
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名稱>
FOR EACH ROW
<觸發(fā)器SQL語(yǔ)句>
規(guī)則:
1.觸發(fā)器必須有名字,最多64個(gè)字符,可能后面會(huì)附有分隔符.它和MySQL中其他對(duì)象的命名方式基本相象.
2.我們不能給同一張表的同一個(gè)事件安排兩個(gè)觸發(fā)器
3.FOR EACH ROW子句通知觸發(fā)器每隔一行執(zhí)行一次動(dòng)作,而不是對(duì)整個(gè)表執(zhí)行一次。
4.你必須擁有相當(dāng)大的權(quán)限才能創(chuàng)建觸發(fā)器(CREATE TRIGGER)。
5.觸發(fā)器包含所要觸發(fā)的SQL語(yǔ)句:這里的語(yǔ)句可以是任何合法的語(yǔ)句,包括復(fù)合語(yǔ)句,但是這里的語(yǔ)句受的限制和函數(shù)的一樣:
ALTER 'CACHE INDEX' CALL COMMIT CREATE DELETE
DROP 'FLUSH PRIVILEGES' GRANT INSERT KILL
LOCK OPTIMIZE REPAIR REPLACE REVOKE
ROLLBACK SAVEPOINT 'SELECT FROM table'
'SET system variable' 'SET TRANSACTION'
SHOW 'START TRANSACTION' TRUNCATE UPDATE
6.可以使用NEW與OLD關(guān)鍵字來訪問更新前后的數(shù)據(jù)
用觸發(fā)器編寫加密
aes_encryp加密函數(shù)
mysql> delimiter //
mysql> create trigger t_customer_insert before insert on customer
-> for each row
-> begin
-> set NEW.customer_ssn = aes_encrypt(NEW.customer_ssn,'password');
-> end;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> insert into customer values (1,'fred','smith','456097234');
Query OK, 1 row affected (0.00 sec)
mysql> select * from customer;
用觸發(fā)器更新記錄字段的長(zhǎng)度CHAR_LENGTH()函數(shù)
mysql> CREATE TABLE data (name VARCHAR(255));
Query OK, 0 rows affected (0.09 sec)
mysql> CREATE TABLE chars (count INT(10));
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO chars (count) VALUES (0);
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TRIGGER t1 AFTER INSERT ON
data FOR EACH ROW UPDATE chars SET count
= count + CHAR_LENGTH(NEW.name);
Query OK, 0 rows affected (0.01 sec)
相反的
CREATE TRIGGER t2
AFTER delete ON data
FOR EACH ROW
begin
UPDATE chars SET count = count - CHAR_LENGTH(old.name);
end;
//