MySQL中怎么创建一个触发器
发布时间：2022-02-10 13:17:38 所属栏目：MySql教程 来源：互联网
导读：MySQL中怎么创建一个触发器，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。 --创建测试表 MariaDB [test] create table account (acct_num int, amount decimal(10,2));
 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 triggersG *************************** 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) 看完上述内容是否对您有帮助呢？ （编辑：武汉站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
