- 论坛徽章:
- 0
|
0.触发器是对insert update delete 等操作实现数据的一致性操作
1.基本格式: create trigger 触发器名 before|after 触发事件(insert|update|delete) on 表名 for each row 执行语句
parameter instruction: for each row : 任何记录满足触发条件都会引发触发器
mysql> create trigger dept_trig1 before insert on department for each row insert into trigger_time values(now());
Query OK, 0 rows affected (0.08 sec)
create trigger 触发器名 before|after 触发事件 on 表名 for each row begin 执行语句 end
查看触发器: show triggers ;
在trigger表中查看触发器信息: select * from information_schema.triggers;
mysql> show tables; +----------------+ | Tables_in_exam | +----------------+ | operate | | product | +----------------+ 2 rows in set (0.00 sec)
mysql> desc product; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(4) | NO | PRI | NULL | | | name | varchar(12) | NO | | NULL | | | function | varchar(20) | NO | | NULL | | | address | varchar(20) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
mysql> desc operate; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | op_id | int(4) | NO | PRI | NULL | auto_increment | | op_name | varchar(12) | NO | | NULL | | | op_time | time | NO | | NULL | | +---------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
mysql> create trigger prd_bf_insert before insert -> on product for each row -> insert into operate values(null,'insert product',now()); Query OK, 0 rows affected (0.07 sec)
mysql> show triggers\G *************************** 1. row *************************** Trigger: prd_bf_insert Event: INSERT Table: product Statement: insert into operate values(null,'insert product',now()) Timing: BEFORE Created: NULL sql_mode: Definer: root@localhost character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)
mysql> create trigger prd_af_update after update -> on product for each row -> insert into operate values(null,'update product',now()); Query OK, 0 rows affected (0.07 sec)
mysql> select * from information_schema.triggers where trigger_name='prd_af_update'\G *************************** 1. row *************************** TRIGGER_CATALOG: NULL TRIGGER_SCHEMA: exam TRIGGER_NAME: prd_af_update EVENT_MANIPULATION: UPDATE EVENT_OBJECT_CATALOG: NULL EVENT_OBJECT_SCHEMA: exam EVENT_OBJECT_TABLE: product ACTION_ORDER: 0 ACTION_CONDITION: NULL ACTION_STATEMENT: insert into operate values(null,'update product',now()) ACTION_ORIENTATION: ROW ACTION_TIMING: AFTER ACTION_REFERENCE_OLD_TABLE: NULL ACTION_REFERENCE_NEW_TABLE: NULL ACTION_REFERENCE_OLD_ROW: OLD ACTION_REFERENCE_NEW_ROW: NEW CREATED: NULL SQL_MODE: DEFINER: root@localhost CHARACTER_SET_CLIENT: latin1 COLLATION_CONNECTION: latin1_swedish_ci DATABASE_COLLATION: latin1_swedish_ci 1 row in set (0.00 sec)
mysql> create trigger prd_af_del after delete -> on product for each row -> insert into operate values(null,'delete product',now()); Query OK, 0 rows affected (0.08 sec)
mysql> insert into product values(1001,'abc','for-cold','beijing'); Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from operate; +-------+--------------+----------+ | op_id | op_name | op_time | +-------+--------------+----------+ | 1 | insert produ | 19:58:45 | +-------+--------------+----------+ 1 row in set (0.00 sec)
mysql> update product set address='tianjing' where id=1001; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1
mysql> select * from product; +------+------+----------+----------+ | id | name | function | address | +------+------+----------+----------+ | 1001 | abc | for-cold | tianjing | +------+------+----------+----------+ 1 row in set (0.00 sec)
mysql> select * from operate; +-------+--------------+----------+ | op_id | op_name | op_time | +-------+--------------+----------+ | 1 | insert produ | 19:58:45 | | 2 | update produ | 20:01:54 | +-------+--------------+----------+ 2 rows in set (0.00 sec)
mysql> delete from product where id=1001; Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from product; Empty set (0.00 sec)
mysql> select *from operate; +-------+--------------+----------+ | op_id | op_name | op_time | +-------+--------------+----------+ | 1 | insert produ | 19:58:45 | | 2 | update produ | 20:01:54 | | 3 | delete produ | 20:04:01 | +-------+--------------+----------+ 3 rows in set (0.00 sec)
mysql> drop trigger prd_bf_insert; Query OK, 0 rows affected (0.04 sec)
mysql> select * from information_schema.triggers where trigger_name='prd_bf_insert'; Empty set (0.00 sec)
以上就是 创建 查看 删除触发器的基本操作 以及触发器的作用
|
|