mysql自动创建触发器_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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2121396/，如需转载，请注明出处，否则将追究法律责任。