mysql触发器的例子--插入前更新数据 - snowfly123

公告

本文介绍下，一个mysql触发器的例子，在数据插入前更新相关内容，有需要的朋友参考下。
mysql触发器的例子，如下：
view source
print?

             001
             mysql> CREATE TABLE Employee(
         
             002
                 ->     id            int,
         
             003
                 ->     first_name    VARCHAR(15),
         
             004
                 ->     last_name     VARCHAR(15),
         
             005
                 ->     start_date    DATE,
         
             006
                 ->     end_date      DATE,
         
             007
                 ->     salary        FLOAT(8,2),
         
             008
                 ->     city          VARCHAR(10),
         
             009
                 ->     description   VARCHAR(15)
         
             010
                 -> );
         
             011
             Query OK, 0 rows affected (0.03 sec)
         
             012
              
             013
             mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
         
             014
                 ->              values (1,'Jason',    'Martin',  '19960725',  '20060725', 1234.56,'Toronto',  'Programmer');
         
             015
             Query OK, 1 row affected (0.02 sec)
         
             016
              
             017
             mysql>
         
             018
             mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
         
             019
                 ->               values(2,'Alison',   'Mathews',  '19760321', '19860221', 6661.78,'Vancouver','Tester');
         
             020
             Query OK, 1 row affected (0.00 sec)
         
             021
              
             022
             mysql>
         
             023
             mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
         
             024
                 ->               values(3,'James',    'Smith',    '19781212', '19900315', 6544.78,'Vancouver','Tester');
         
             025
             Query OK, 1 row affected (0.00 sec)
         
             026
              
             027
             mysql>
         
             028
             mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
         
             029
                 ->               values(4,'Celia',    'Rice',     '19821024', '19990421', 2344.78,'Vancouver','Manager');
         
             030
             Query OK, 1 row affected (0.00 sec)
         
             031
              
             032
             mysql>
         
             033
             mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
         
             034
                 ->               values(5,'Robert',   'Black',    '19840115', '19980808', 2334.78,'Vancouver','Tester');
         
             035
             Query OK, 1 row affected (0.00 sec)
         
             036
              
             037
             mysql>
         
             038
             mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
         
             039
                 ->               values(6,'Linda',    'Green',    '19870730', '19960104', 4322.78,'New York',  'Tester');
         
             040
             Query OK, 1 row affected (0.00 sec)
         
             041
              
             042
             mysql>
         
             043
             mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
         
             044
                 ->               values(7,'David',    'Larry',    '19901231', '19980212', 7897.78,'New York',  'Manager');
         
             045
             Query OK, 1 row affected (0.00 sec)
         
             046
              
             047
             mysql>
         
             048
             mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
         
             049
                 ->               values(8,'James',    'Cat',     '19960917',  '20020415', 1232.78,'Vancouver', 'Tester');
         
             050
             Query OK, 1 row affected (0.01 sec)
         
             051
              
             052
             mysql>
         
             053
             mysql> select * from Employee;
         
             054
             +------+------------+-----------+------------+------------+---------+-----------+-------------+
         
             055
             | id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
         
             056
             +------+------------+-----------+------------+------------+---------+-----------+-------------+
         
             057
             |    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
         
             058
             |    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
         
             059
             |    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
         
             060
             |    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
         
             061
             |    5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
         
             062
             |    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester      |
         
             063
             |    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager     |
         
             064
             |    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
         
             065
             +------+------------+-----------+------------+------------+---------+-----------+-------------+
         
             066
             8 rows in set (0.00 sec)
         
             067
              
             068
             mysql> DELIMITER //
         
             069
             mysql>
         
             070
             mysql> CREATE TRIGGER myTrigger BEFORE UPDATE ON employee //创建mysql触发器
         
             071
                 -> FOR EACH ROW
         
             072
                 -> BEGIN
         
             073
                 ->
         
             074
                 -> IF NEW.id > 5 THEN
         
             075
                 ->     SET NEW.first_name = NEW.first_name+ " *";
         
             076
                 -> END IF;
         
             077
                 ->
         
             078
                 -> IF NEW.salary IS NULL OR NEW.salary = 0 THEN
         
             079
                 ->         SET NEW.salary = 100;
         
             080
                 -> ELSE
         
             081
                 ->         SET NEW.salary = NEW.salary + 100;
         
             082
                 -> END IF;
         
             083
                 ->
         
             084
                 -> END
         
             085
                 ->
         
             086
                 -> //
         
             087
             Query OK, 0 rows affected (0.00 sec)
         
             088
              
             089
             mysql> DELIMITER ;
         
             090
             mysql>
         
             091
             mysql> update employee set id = 6, first_name= 'new', salary = null; //更新数据
         
             092
             Query OK, 8 rows affected, 8 warnings (0.00 sec)
         
             093
             Rows matched: 8  Changed: 8  Warnings: 0
         
--// www.jbxue.com

             095
             mysql>
         
             096
             mysql> select * from employee;
         
             097
             +------+------------+-----------+------------+------------+--------+-----------+-------------+
         
             098
             | id   | first_name | last_name | start_date | end_date   | salary | city      | description |
         
             099
             +------+------------+-----------+------------+------------+--------+-----------+-------------+
         
             100
             |    6 | 0          | Martin    | 1996-07-25 | 2006-07-25 | 100.00 | Toronto   | Programmer  |
         
             101
             |    6 | 0          | Mathews   | 1976-03-21 | 1986-02-21 | 100.00 | Vancouver | Tester      |
         
             102
             |    6 | 0          | Smith     | 1978-12-12 | 1990-03-15 | 100.00 | Vancouver | Tester      |
         
             103
             |    6 | 0          | Rice      | 1982-10-24 | 1999-04-21 | 100.00 | Vancouver | Manager     |
         
             104
             |    6 | 0          | Black     | 1984-01-15 | 1998-08-08 | 100.00 | Vancouver | Tester      |
         
             105
             |    6 | 0          | Green     | 1987-07-30 | 1996-01-04 | 100.00 | New York  | Tester      |
         
             106
             |    6 | 0          | Larry     | 1990-12-31 | 1998-02-12 | 100.00 | New York  | Manager     |
         
             107
             |    6 | 0          | Cat       | 1996-09-17 | 2002-04-15 | 100.00 | Vancouver | Tester      |
         
             108
             +------+------------+-----------+------------+------------+--------+-----------+-------------+
         
             109
             8 rows in set (0.00 sec)
         
             110
              
             111
             mysql>
         
             112
             mysql> update employee set id = 7, first_name= 'new', salary = 100; //更新数据
         
             113
             Query OK, 8 rows affected, 8 warnings (0.00 sec)
         
             114
             Rows matched: 8  Changed: 8  Warnings: 0
         
             115
              
             116
             mysql>
         
             117
             mysql> select * from employee;
         
             118
             +------+------------+-----------+------------+------------+--------+-----------+-------------+
         
             119
             | id   | first_name | last_name | start_date | end_date   | salary | city      | description |
         
             120
             +------+------------+-----------+------------+------------+--------+-----------+-------------+
         
             121
             |    7 | 0          | Martin    | 1996-07-25 | 2006-07-25 | 200.00 | Toronto   | Programmer  |
         
             122
             |    7 | 0          | Mathews   | 1976-03-21 | 1986-02-21 | 200.00 | Vancouver | Tester      |
         
             123
             |    7 | 0          | Smith     | 1978-12-12 | 1990-03-15 | 200.00 | Vancouver | Tester      |
         
             124
             |    7 | 0          | Rice      | 1982-10-24 | 1999-04-21 | 200.00 | Vancouver | Manager     |
         
             125
             |    7 | 0          | Black     | 1984-01-15 | 1998-08-08 | 200.00 | Vancouver | Tester      |
         
             126
             |    7 | 0          | Green     | 1987-07-30 | 1996-01-04 | 200.00 | New York  | Tester      |
         
             127
             |    7 | 0          | Larry     | 1990-12-31 | 1998-02-12 | 200.00 | New York  | Manager     |
         
             128
             |    7 | 0          | Cat       | 1996-09-17 | 2002-04-15 | 200.00 | Vancouver | Tester      |
         
             129
             +------+------------+-----------+------------+------------+--------+-----------+-------------+
         
             130
             8 rows in set (0.00 sec)
         
             131
              
             132
             mysql> drop table Employee;
         
             133
             Query OK, 0 rows affected (0.02 sec)
本文原始链接：http://www.jbxue.com/db/11615.html
posted on 2013-10-29 06:26 snowfly123 阅读(628) 评论(0) 编辑收藏举报
会员力量，点亮园子希望
刷新页面返回顶部
好好学习，东方不败。

公告
