本文介绍下，一个mysql触发器的例子，在数据插入前更新相关内容，有需要的朋友参考下。
mysql触发器的例子，如下：
001 |
mysql> CREATE TABLE Employee(
|
003 |
-> first_name VARCHAR(15),
|
004 |
-> last_name VARCHAR(15),
|
007 |
-> salary FLOAT(8,2),
|
009 |
-> description VARCHAR(15)
|
011 |
Query OK, 0 rows affected (0.03 sec)
|
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) |
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) |
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) |
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) |
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) |
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) |
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) |
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) |
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)
|
070 |
mysql> CREATE TRIGGER myTrigger BEFORE UPDATE ON employee //创建mysql触发器
|
074 |
-> IF NEW.id > 5 THEN
|
075 |
-> SET NEW.first_name = NEW.first_name+ " *";
|
078 |
-> IF NEW.salary IS NULL OR NEW.salary = 0 THEN
|
079 |
-> SET NEW.salary = 100;
|
081 |
-> SET NEW.salary = NEW.salary + 100;
|
087 |
Query OK, 0 rows affected (0.00 sec)
|
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
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)
|
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
|
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)
|
132 |
mysql> drop table Employee;
|
133 |
Query OK, 0 rows affected (0.02 sec)
|
本文原始链接：http://www.jbxue.com/db/11615.html