MySQL 建立觸發器
--建立測試表
MariaDB [test]> create table account (acct_num int, amount decimal(10,2));
Query OK, 0 rows affected (0.16 sec)
--建立觸發器
MariaDB [test]> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.08 sec)
MariaDB [test]> select @sum;
+------+
| @sum |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
MariaDB [test]> SET @sum = 0;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [test]> select @sum;
+---------+
| @sum |
+---------+
| 1852.48 |
+---------+
1 row in set (0.00 sec)
MariaDB [test]> select sum(amount) from account;
+-------------+
| sum(amount) |
+-------------+
| 1852.48 |
+-------------+
1 row in set (0.00 sec)
--檢視觸發器
MariaDB [test]> show triggers\G
*************************** 1. row ***************************
Trigger: ins_sum
Event: INSERT
Table: account
Statement: SET @sum = @sum + NEW.amount
Timing: BEFORE
Created: NULL
sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
MariaDB [test]> select * from information_schema.triggers where trigger_name='ins_sum' \G
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: test
TRIGGER_NAME: ins_sum
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: test
EVENT_OBJECT_TABLE: account
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: SET @sum = @sum + NEW.amount
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)
MariaDB [test]> create table account (acct_num int, amount decimal(10,2));
Query OK, 0 rows affected (0.16 sec)
--建立觸發器
MariaDB [test]> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.08 sec)
MariaDB [test]> select @sum;
+------+
| @sum |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
MariaDB [test]> SET @sum = 0;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [test]> select @sum;
+---------+
| @sum |
+---------+
| 1852.48 |
+---------+
1 row in set (0.00 sec)
MariaDB [test]> select sum(amount) from account;
+-------------+
| sum(amount) |
+-------------+
| 1852.48 |
+-------------+
1 row in set (0.00 sec)
--檢視觸發器
MariaDB [test]> show triggers\G
*************************** 1. row ***************************
Trigger: ins_sum
Event: INSERT
Table: account
Statement: SET @sum = @sum + NEW.amount
Timing: BEFORE
Created: NULL
sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
MariaDB [test]> select * from information_schema.triggers where trigger_name='ins_sum' \G
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: test
TRIGGER_NAME: ins_sum
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: test
EVENT_OBJECT_TABLE: account
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: SET @sum = @sum + NEW.amount
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26506993/viewspace-2121396/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 建立MySQL觸發器MySql觸發器
- mysql建立觸發器MySql觸發器
- 建立SQL觸發器SQL觸發器
- MySQL觸發器MySql觸發器
- mysql——觸發器MySql觸發器
- mysql 觸發器MySql觸發器
- Mysql觸發器:MySql觸發器
- Oracle觸發器6(建立系統事件觸發器)Oracle觸發器事件
- MySQL建立觸發器時報錯Error Code: 1064MySql觸發器Error
- MySQL使用觸發器MySql觸發器
- MySql-觸發器MySql觸發器
- MySQL觸發器介紹MySql觸發器
- MySQL6:觸發器MySql觸發器
- mysql繞過行觸發器,實現語句觸發器MySql觸發器
- SqlServer觸發器的建立與使用SQLServer觸發器
- SqlServer-建立觸發器例項SQLServer觸發器
- (15)mysql 中的觸發器MySql觸發器
- 簡單建立序列和觸發器示例觸發器
- SQLite建立觸發器 CREATE TRIGGERSQLite觸發器
- MySQL觸發器的使用規則MySql觸發器
- Mysql之觸發器triggerMySql觸發器
- MySQL入門-- TRIGGER(觸發器)MySql觸發器
- MySQL全面瓦解17:觸發器相關MySql觸發器
- MySQL學習筆記--觸發器的建立與刪除及使用注意事項MySql筆記觸發器
- mysql主從和觸發器的關係MySql觸發器
- mysql 觸發器/過程中的變數!!MySql觸發器變數
- oracle 觸發器實現禁止在資料庫中建立dblink ---透過觸發器實現Oracle觸發器資料庫
- Oracle觸發器觸發級別Oracle觸發器
- MySQL與SQL的觸發器的不同寫法MySql觸發器
- 根據業務寫觸發器(oracle觸發器片)觸發器Oracle
- MySQL觸發器使用MySql觸發器
- SQL觸發器SQL觸發器
- Oracle觸發器Oracle觸發器
- 【SQL Server】-- 一觸即發之觸發器SQLServer觸發器
- 【MySQL】MySQL(三)儲存過程和函式、觸發器、事務MySql儲存過程函式觸發器
- SQL Server觸發器建立、刪除、修改、檢視示例步驟SQLServer觸發器
- mysql使用者建立觸發器許可權不足跟引數log_bin_trust_function_creatorsMySql觸發器RustFunction
- MySQL / MariaDB 觸發器的建立、使用、檢視、刪除教程及應用場景實戰案例MySql觸發器