乱爱性全过程免费视频/午夜毛片视频/中文字幕av三区/一级视频网址

當前位置： 維易PHP培訓學院 > MYSQL教程 > 內容正文

Mysql學習MySQL中觸發器的基礎學習教程

作者：VEPHP   時間 2017-10-01

《Mysql學習MySQL中觸發器的基礎學習教程》要點：
本文介紹了Mysql學習MySQL中觸發器的基礎學習教程，希望對您有用。如果有疑問，可以聯系我們。

0.觸發器的基本概念
觸發器是一種特殊的存儲過程,它在插入,刪除或修改特定表中的數據時觸發執行,它比數據庫本身標準的功能有更精細和更復雜的數據控制能力.MYSQL學習

數據庫觸發器有以下的作用：MYSQL學習

(1).安全性.可以基于數據庫的值使用戶具有操作數據庫的某種權利.MYSQL學習

? # 可以基于時間限制用戶的操作,例如不允許下班后和節假日修改數據庫數據.MYSQL學習

? # 可以基于數據庫中的數據限制用戶的操作,例如不允許股票的價格的升幅一次超過10%.MYSQL學習

(2).審計.可以跟蹤用戶對數據庫的操作.??MYSQL學習

? # 審計用戶操作數據庫的語句.MYSQL學習

? # 把用戶對數據庫的更新寫入審計表.MYSQL學習

(3).實現復雜的數據完整性規則MYSQL學習

? # 實現非標準的數據完整性檢查和約束.觸發器可產生比規則更為復雜的限制.與規則不同,觸發器可以引用列或數據庫對象.例如,觸發器可回退任何企圖吃進超過自己保證金的期貨.MYSQL學習


? # 提供可變的缺省值.MYSQL學習

(4).實現復雜的非標準的數據庫相關完整性規則.觸發器可以對數據庫中相關的表進行連環更新.例如,在auths表author_code列上的刪除觸發器可導致相應刪除在其它表中的與之匹配的行.MYSQL學習

? # 在修改或刪除時級聯修改或刪除其它表中的與之匹配的行.MYSQL學習

? # 在修改或刪除時把其它表中的與之匹配的行設成NULL值.MYSQL學習

? # 在修改或刪除時把其它表中的與之匹配的行級聯設成缺省值.MYSQL學習

? # 觸發器能夠拒絕或回退那些破壞相關完整性的變化,取消試圖進行數據更新的事務.當插入一個與其主健不匹配的外部鍵時,這種觸發器會起作用.例如,可以在books.author_code 列上生成一個插入觸發器,如果新值與auths.author_code列中的某值不匹配時,插入被回退.MYSQL學習

(5).同步實時地復制表中的數據.MYSQL學習

(6).自動計算數據值,如果數據的值達到了一定的要求,則進行特定的處理.例如,如果公司的帳號上的資金低于5萬元則立即給財務人員發送警告數據.MYSQL學習


1. 創建觸發器語法MYSQL學習

CREATE
 [DEFINER = { user | CURRENT_USER }]
 TRIGGER trigger_name
 trigger_time trigger_event
 ON tbl_name FOR EACH ROW
 trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
CREATE
 [DEFINER = { user | CURRENT_USER }]
 TRIGGER trigger_name
 trigger_time trigger_event
 ON tbl_name FOR EACH ROW
 trigger_body
 
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }

語法相關部分說明：
1.1 授權與回收
創建觸發器需要有CREATE TRIGGER權限：MYSQL學習

grant create trigger on `database_naem`.`table_name` to `user_name`@`ip_address`;
grant create trigger on `database_naem`.`table_name` to `user_name`@`ip_address`;

權限收回：MYSQL學習

revoke create trigger on `database_naem`.`table_name` from `user_name`@`ip_address`;
revoke create trigger on `database_naem`.`table_name` from `user_name`@`ip_address`;

1.2 trigger_name
必須給觸發器命令,最多64個字符,建議用表的名字_觸發器類型的縮寫方法命名.如ttlsa_posts_bi(表ttlsa_posts,觸發器發生在insert之前before)
1.3 DEFINER子句
在激活觸發器時,檢查訪問權限,確保觸發器安全使用.
1.4 trigger_time
定義觸發器觸發時間.可以設置為在行記錄更改之前或之后發生.
1.5 trigger_event
定義觸發器觸發事件.觸發的事件有：
1.5.1
INSERT：當一個新行插入到表中時觸發.如INSERT、LOAD DATA和REPLACE語句.
UPDATE：當一個行數據被更改時觸發.如UPDATE語句.
DELETE：當一個行從表中刪除時觸發.如DELETE和REPLACE語句. 注意：DROP TABLE和TRUNCATE TABLE語句不會觸發該觸發器,因為它們不是使用DELETE.同樣刪除一個分區表也不會觸發.
有一個潛在的混亂情況,如INSERT INTO ... ON DUPLICATE KEY UPDATE ... 取決于是否有重復鍵行.
不能對一個表創建具有相同的觸發事件和觸發時間的多個觸發器.如對于一個表不能創建兩個BEFORE UPDATE觸發器,但是,可以創建一個BEFORE UPDATE和一個BEFORE INSERT或一個BEFORE UPDATE和一個AFTER UPDATE觸發器.
1.6 FOR EACH ROW子句
定義觸發執行間隔.FOR EACH ROW子句定義觸發器每隔一行執行一次動作,而不是對整個表執行一次.
1.7 trigger_body子句
包含要觸發執行的SQL語句.可以是任何合法的語句,包括復合語句(需要使用BEGIN ... END結構),流控制語句(if、case、while、loop、for、repeat、leave、iterate),變量聲明(declare)以及指派(set),異常處理聲明,允許條件聲明,但是這里的語句受的限制和函數的一樣.
1.7.1 OLD與NEW
在觸發器的SQL語句中,可以關聯表中的任何列,通過使用OLD和NEW列名來標識,如OLD.col_name、NEW.col_name.OLD.col_name關聯現有的行的一列在被更新或刪除前的值.NEW.col_name關聯一個新行的插入或更新現有的行的一列的值.
對于INSERT語句,只有NEW是合法的.否則會報錯：ERROR 1363 (HY000): There is no OLD row in on INSERT trigger
對于DELETE語句,只有OLD是合法的.否則會報錯：ERROR 1363 (HY000): There is no NEW row in on DELETE trigger
對于UPDATE語句,NEW和OLD可以同時使用.
2. 實例
2.1 創建表
使用在《mysqludf_json將關系數據以JSON編碼》一文中創建的表.后續會將用戶表遷移到nosql數據庫上的.MYSQL學習

mysql> create table `ttlsa_users` (
  -> `uid` int(11) unsigned,
  -> `username` varchar(40) NOT NULL,
  -> `password` varchar(40) NOT NULL,
  -> `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  -> PRIMARY KEY (`uid`)
  -> );
mysql> create table `ttlsa_users` (
  -> `uid` int(11) unsigned,
  -> `username` varchar(40) NOT NULL,
  -> `password` varchar(40) NOT NULL,
  -> `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  -> PRIMARY KEY (`uid`)
  -> );

創建另外一張表來存放觸發器動作數據.MYSQL學習

mysql> create table `ttlsa_users3` (
  -> `uid` int(11) unsigned,
  -> `userinfo` varchar(200),
  -> );
mysql> create table `ttlsa_users3` (
  -> `uid` int(11) unsigned,
  -> `userinfo` varchar(200),
  -> );

2.2 創建觸發器MYSQL學習

mysql> delimiter //
mysql> create trigger ttlsa_users_ai
  -> after insert on ttlsa_users
  -> for each row
  -> insert into ttlsa_users3 (uid, userinfo) values(uid, json_object(NEW.uid, NEW.username, NEW.password));
  -> //
mysql> create trigger ttlsa_users_au
  -> after update on ttlsa_users
  -> for each row
  -> update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid;
  -> //
mysql> delimiter //
mysql> create trigger ttlsa_users_ai
  -> after insert on ttlsa_users
  -> for each row
  -> insert into ttlsa_users3 (uid, userinfo) values(uid, json_object(NEW.uid, NEW.username, NEW.password));
  -> //
 
mysql> create trigger ttlsa_users_au
  -> after update on ttlsa_users
  -> for each row
  -> update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid;
  -> //

2.3 測試MYSQL學習

mysql> insert into ttlsa_users values (890,'xuhh',md5('abc'),NULL,'test trigger')//

Query OK, 1 row affected (0.01 sec)

mysql> select * from ttlsa_users//

+-----+-------------+----------------------------------+---------------------+------------------------------------+
| uid | username  | password             | createtime     | json_data             |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" |
| 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh                |
| 890 | xuhh    | 900150983cd24fb0d6963f7d28e17f72 | 2013-08-14 16:40:49 | test trigger            |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from ttlsa_users3//

+-----------------------------------------------------------------------------+------+
| userinfo                                  | uid |
+-----------------------------------------------------------------------------+------+
| {"uid":890,"username":"xuhh","password":"900150983cd24fb0d6963f7d28e17f72"} | 890 |
+-----------------------------------------------------------------------------+------+
2 rows in set (0.00 sec)

mysql> update ttlsa_users set password='test_update' where uid=890//

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from ttlsa_users//

+-----+-------------+----------------------------------+---------------------+------------------------------------+
| uid | username  | password             | createtime     | json_data             |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" |
| 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh                |
| 890 | xuhh    | test_update           | 2013-08-14 16:41:33 | test trigger            |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from ttlsa_users3//

+-----------------------------------------------------------------------------+------+
| userinfo                                  | uid |
+-----------------------------------------------------------------------------+------+
| {"uid":890,"username":"xuhh","password":"test_update"}           | 890 |
+-----------------------------------------------------------------------------+------+
2 rows in set (0.00 sec)

mysql> insert into ttlsa_users values (890,'xuhh',md5('abc'),NULL,'test trigger')//

Query OK, 1 row affected (0.01 sec)

?
MYSQL學習

mysql> select * from ttlsa_users//

+-----+-------------+----------------------------------+---------------------+------------------------------------+
| uid | username  | password             | createtime     | json_data             |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" |
| 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh                |
| 890 | xuhh    | 900150983cd24fb0d6963f7d28e17f72 | 2013-08-14 16:40:49 | test trigger            |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
3 rows in set (0.00 sec)

?
MYSQL學習

mysql> select * from ttlsa_users3//

+-----------------------------------------------------------------------------+------+
| userinfo                                  | uid |
+-----------------------------------------------------------------------------+------+
| {"uid":890,"username":"xuhh","password":"900150983cd24fb0d6963f7d28e17f72"} | 890 |
+-----------------------------------------------------------------------------+------+
2 rows in set (0.00 sec)
 

mysql> update ttlsa_users set password='test_update' where uid=890//

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

?
MYSQL學習

mysql> select * from ttlsa_users//

+-----+-------------+----------------------------------+---------------------+------------------------------------+
| uid | username  | password             | createtime     | json_data             |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" |
| 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh                |
| 890 | xuhh    | test_update           | 2013-08-14 16:41:33 | test trigger            |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
3 rows in set (0.00 sec)

?
MYSQL學習

mysql> select * from ttlsa_users3//

+-----------------------------------------------------------------------------+------+
| userinfo                                  | uid |
+-----------------------------------------------------------------------------+------+
| {"uid":890,"username":"xuhh","password":"test_update"}           | 890 |
+-----------------------------------------------------------------------------+------+
2 rows in set (0.00 sec)

3. 管理
3.1 列出觸發器MYSQL學習

mysql> SHOW TRIGGERS like '%ttlsa%'; 觸發器名稱匹配%ttlsa%

*************************** 1. row ***************************
       Trigger: ttlsa_users_ai
        Event: INSERT
        Table: ttlsa_users
      Statement: insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password))
       Timing: AFTER
       Created: NULL
      sql_mode: NO_ENGINE_SUBSTITUTION
       Definer: root@127.0.0.1
character_set_client: utf8
collation_connection: utf8_general_ci
 Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
       Trigger: ttlsa_users_au
        Event: UPDATE
        Table: ttlsa_users
      Statement: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid
       Timing: AFTER
       Created: NULL
      sql_mode: NO_ENGINE_SUBSTITUTION
       Definer: root@127.0.0.1
character_set_client: utf8
collation_connection: utf8_general_ci
 Database Collation: latin1_swedish_ci
2 rows in set (0.00 sec)

mysql> SHOW TRIGGERS; #列出所有
mysql> SHOW TRIGGERS from database_name; #列出數據庫的觸發器
mysql> SHOW CREATE TRIGGER trigger_name;  #查看創建觸發器

*************************** 1. row ***************************
        Trigger: ttlsa_users_ai
       sql_mode: NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`127.0.0.1` trigger ttlsa_users_ai after insert on ttlsa_users for each row insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password))
 character_set_client: utf8
 collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.01 sec)

3.2 INFORMATION_SCHEMA.TRIGGERS表MYSQL學習

sql> SHOW TRIGGERS like '%ttlsa%'; #觸發器名稱匹配%ttlsa%

*************************** 1. row ***************************
       Trigger: ttlsa_users_ai
        Event: INSERT
        Table: ttlsa_users
      Statement: insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password))
       Timing: AFTER
       Created: NULL
      sql_mode: NO_ENGINE_SUBSTITUTION
       Definer: root@127.0.0.1
character_set_client: utf8
collation_connection: utf8_general_ci
 Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
       Trigger: ttlsa_users_au
        Event: UPDATE
        Table: ttlsa_users
      Statement: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid
       Timing: AFTER
       Created: NULL
      sql_mode: NO_ENGINE_SUBSTITUTION
       Definer: root@127.0.0.1
character_set_client: utf8
collation_connection: utf8_general_ci
 Database Collation: latin1_swedish_ci
2 rows in set (0.00 sec)

mysql> SHOW TRIGGERS; #列出所有
mysql> SHOW TRIGGERS from database_name; #列出數據庫的觸發器
mysql> SHOW CREATE TRIGGER trigger_name;  #查看創建觸發器

*************************** 1. row ***************************
        Trigger: ttlsa_users_ai
       sql_mode: NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`127.0.0.1` trigger ttlsa_users_ai after insert on ttlsa_users for each row insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password))
 character_set_client: utf8
 collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.01 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='ttlsa_users_au'\G

*************************** 1. row ***************************
      TRIGGER_CATALOG: def
      TRIGGER_SCHEMA: test
       TRIGGER_NAME: ttlsa_users_au
    EVENT_MANIPULATION: UPDATE
   EVENT_OBJECT_CATALOG: def
    EVENT_OBJECT_SCHEMA: test
    EVENT_OBJECT_TABLE: ttlsa_users
       ACTION_ORDER: 0
     ACTION_CONDITION: NULL
     ACTION_STATEMENT: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid
    ACTION_ORIENTATION: ROW
       ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
 ACTION_REFERENCE_OLD_ROW: OLD
 ACTION_REFERENCE_NEW_ROW: NEW
          CREATED: NULL
         SQL_MODE: NO_ENGINE_SUBSTITUTION
          DEFINER: root@127.0.0.1
   CHARACTER_SET_CLIENT: utf8
   COLLATION_CONNECTION: utf8_general_ci
    DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='ttlsa_users_au'\G

*************************** 1. row ***************************
      TRIGGER_CATALOG: def
      TRIGGER_SCHEMA: test
       TRIGGER_NAME: ttlsa_users_au
    EVENT_MANIPULATION: UPDATE
   EVENT_OBJECT_CATALOG: def
    EVENT_OBJECT_SCHEMA: test
    EVENT_OBJECT_TABLE: ttlsa_users
       ACTION_ORDER: 0
     ACTION_CONDITION: NULL
     ACTION_STATEMENT: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid
    ACTION_ORIENTATION: ROW
       ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
 ACTION_REFERENCE_OLD_ROW: OLD
 ACTION_REFERENCE_NEW_ROW: NEW
          CREATED: NULL
         SQL_MODE: NO_ENGINE_SUBSTITUTION
          DEFINER: root@127.0.0.1
   CHARACTER_SET_CLIENT: utf8
   COLLATION_CONNECTION: utf8_general_ci
    DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.00 sec)

3.3 刪除觸發器MYSQL學習

mysql> drop trigger trigger_name;
mysql> drop trigger trigger_name;

?MYSQL學習

轉載請注明本頁網址：
http://www.fzlkiss.com/jiaocheng/5961.html


    標簽：