<ruby id="bdb3f"></ruby>

    <p id="bdb3f"><cite id="bdb3f"></cite></p>

      <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
        <p id="bdb3f"><cite id="bdb3f"></cite></p>

          <pre id="bdb3f"></pre>
          <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

          <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
          <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

          <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                <ruby id="bdb3f"></ruby>

                # 觸發器 發器的特性: 1. 有begin end體,begin end;之間的語句可以寫的簡單或者復雜 2. 什么條件會觸發:I、D、U 3. 什么時候觸發:在增刪改前或者后 4. 觸發頻率:針對每一行執行 5. 觸發器定義在表上,附著在表上。 也就是由事件來觸發某個操作,事件包括INSERT語句,UPDATE語句和DELETE語句;可以協助應用在數據庫端確保數據的完整性。 ## 創建觸發器 ```mysql CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt ``` trigger_name : 觸發器名稱,用戶自行指定 trigger_time: 觸發時機,取值BEFORE(之前)、AFTER(之后) trigger_event : 出發事件,INSERT、UPDATE、DELETE。(插入、更新、刪除) tbl_name : 需要建立觸發器的表名。 trigger_stmt : 觸發程序體,可以是一條SQL語句或是BEGIN和END包含的索條語句 由上面,可以知道MYSQL可以創建6種類型的觸發器。 (BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE) (AFTER INSERT、AFTER UODATE、AFTER DELETE) 并且一張表上不能創建兩個相同類型的觸發器,因此一張表上面最多能創建6種類型的觸發器。 ### 案例 ```mysql CREATE TRIGGER test_tt AFTER DELETE ON `test` FOR EACH ROW BEGIN DECLARE s VARCHAR(20) DEFAULT 'hello'; SET s = 'world'; UPDATE `member` SET `name` = s WHERE id = OLD.id; END ``` 當在刪除test表的數據時,會在對應的member對應的id中添加name字段值為world。 如果使用下面這種sql語句,一值得到null。 ```mysql CREATE TRIGGER test_tt AFTER DELETE ON `test` FOR EACH ROW BEGIN DECLARE s VARCHAR(20) DEFAULT 'hello'; SET s= (select name from test where id = old.id); SET s = concat('a','hello'); UPDATE `member` SET `name` = s WHERE id = OLD.id; END ``` 這是由于select中old是在數據被刪除了,再去查找,就會一直找不到。 參看下面案例: ```mysql CREATE TRIGGER test_tt AFTER DELETE ON `test` FOR EACH ROW BEGIN UPDATE `member` SET `name` = old.name WHERE id = OLD.id; END ``` 在刪除test表中的一行數據時,能夠把刪除之前的name數據寫入member表中去。 可以見得: old表示的是之前的一整行完整的數據,并且是通過 . 的形式訪問字段內容。 ## 查看觸發器 ### 1、SHOW TRIGGERS語句查看觸發器信息 ```mysql mysql> SHOW TRIGGERS\G; *************************** 1. row *************************** Trigger: add_task_second_update_task Event: INSERT Table: test_custom_task_second Statement: update test_custom_task set `status`= new.`status`,paied=new.paied,mtime=new.mtime where task_id = new.task_id Timing: AFTER Created: NULL sql_mode: Definer: shiwenyuan character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci *************************** 2. row *************************** Trigger: update_task_second_update_task Event: UPDATE Table: test_custom_task_second Statement: update test_custom_task set `status`= new.`status`,settlement_status = new.settlement_status,paied = new.paied ,mtime=new.mtime where task_id = new.task_id Timing: AFTER Created: NULL sql_mode: Definer: shiwenyuan character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 2 rows in set (0.00 sec) ERROR: No query specified ``` ### 2、在information_schema.triggers表中查看觸發器信息 ```mysql mysql> SELECT * FROM information_schema.triggers\G; *************************** 1. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: test TRIGGER_NAME: add_task_second_update_task EVENT_MANIPULATION: INSERT EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: test EVENT_OBJECT_TABLE: test_custom_task_second ACTION_ORDER: 0 ACTION_CONDITION: NULL ACTION_STATEMENT: update test_custom_task set `status`= new.`status`,paied=new.paied,mtime=new.mtime where task_id = new.task_id 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: DEFINER: shiwenyuan CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: utf8_general_ci *************************** 2. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: test TRIGGER_NAME: update_task_second_update_task EVENT_MANIPULATION: UPDATE EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: test EVENT_OBJECT_TABLE: test_custom_task_second ACTION_ORDER: 0 ACTION_CONDITION: NULL ACTION_STATEMENT: update test_custom_task set `status`= new.`status`,settlement_status = new.settlement_status,paied = new.paied ,mtime=new.mtime where task_id = new.task_id 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: DEFINER: shiwenyuan CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: utf8_general_ci 2 rows in set (0.01 sec) ``` 此操作支持 where條件查詢單個 ## 刪除觸發器 ```mysql drop trigger trigger_name ```
                  <ruby id="bdb3f"></ruby>

                  <p id="bdb3f"><cite id="bdb3f"></cite></p>

                    <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
                      <p id="bdb3f"><cite id="bdb3f"></cite></p>

                        <pre id="bdb3f"></pre>
                        <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

                        <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
                        <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

                        <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                              <ruby id="bdb3f"></ruby>

                              哎呀哎呀视频在线观看