mysql--西北狼--第二周--trigger

xiaolilinux

0.觸發(fā)器是對(duì)insert  update delete 等操作實(shí)現(xiàn)數(shù)據(jù)的一致性操作

1.基本格式：
create   trigger  觸發(fā)器名  before|after  觸發(fā)事件(insert|update|delete)
      on     表名    for  each  row 
      執(zhí)行語(yǔ)句

parameter   instruction:
   for  each   row  : 任何記錄滿足觸發(fā)條件都會(huì)引發(fā)觸發(fā)器

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  觸發(fā)器名  before|after    觸發(fā)事件
         on    表名    for  each  row
         begin
            執(zhí)行語(yǔ)句
         end

查看觸發(fā)器：   show  triggers ;

   在trigger表中查看觸發(fā)器信息:    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)

以上就是 創(chuàng)建  查看  刪除觸發(fā)器的基本操作  以及觸發(fā)器的作用