Shulou(Shulou.com)05/31 Report--

MySQL in how to create a trigger, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

-- create a test table

MariaDB [test] > create table account (acct_num int, amount decimal (10j2))

Query OK, 0 rows affected (0.16 sec)

-- create a trigger

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 (137meme14.98), (141meme1937.50), (97qlylyly100.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)

-- View triggers

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)

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.