数据库（SQL）之trigger（触发器）的使用以及视图（view）的基本实现

78 阅读 0 评论 52 点赞

我是靠谱客的博主孝顺毛豆，最近开发中收集的这篇文章主要介绍数据库（SQL）之trigger（触发器）的使用以及视图（view）的基本实现，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

对于触发器，首先需要明确的是一下几点：
这里写图片描述

trigger_name 必须给触发器命令，最多64个字符，建议用表的名字_触发器类型的缩写方法命名。如ttlsa_posts_bi(表ttlsa_posts，触发器发生在insert之前before)
DEFINER子句在激活触发器时，检查访问权限，确保触发器安全使用。
trigger_time 定义触发器触发时间。可以设置为在行记录更改之前或之后发生。
trigger_event 定义触发器触发事件。触发的事件有： INSERT：当一个新行插入到表中时触发。如INSERT、LOAD DATA和REPLACE语句。 UPDATE：当一个行数据被更改时触发。如UPDATE语句。 DELETE：当一个行从表中删除时触发。如DELETE和REPLACE语句。注意：DROP TABLE和TRUNCATE TABLE语句不会触发该触发器，因为它们不是使用DELETE。同样删除一个分区表也不会触发。有一个潜在的混乱情况，如INSERT INTO … ON DUPLICATE KEY UPDATE … 取决于是否有重复键行。不能对一个表创建具有相同的触发事件和触发时间的多个触发器。如对于一个表不能创建两个BEFORE UPDATE触发器，但是，可以创建一个BEFORE UPDATE和一个BEFORE INSERT或一个BEFORE UPDATE和一个AFTER UPDATE触发器。
FOR EACH ROW子句定义触发执行间隔。FOR EACH ROW子句定义触发器每隔一行执行一次动作，而不是对整个表执行一次。
trigger_body子句包含要触发执行的SQL语句。可以是任何合法的语句，包括复合语句(需要使用BEGIN … END结构)，流控制语句(if、case、while、loop、for、repeat、leave、iterate)，变量声明(declare)以及指派(set)，异常处理声明，允许条件声明，但是这里的语句受的限制和函数的一样。
OLD与NEW 在触发器的SQL语句中，可以关联表中的任何列，通过使用OLD和NEW列名来标识，如OLD.col_name、NEW.col_name。OLD.col_name关联现有的行的一列在被更新或删除前的值。NEW.col_name关联一个新行的插入或更新现有的行的一列的值。对于INSERT语句，只有NEW是合法的。否则会报错：ERROR 1363 (HY000): There is no OLD row in on INSERT trigger 对于DELETE语句，只有OLD是合法的。否则会报错：ERROR 1363 (HY000): There is no NEW row in on DELETE trigger 对于UPDATE语句，NEW和OLD可以同时使用。

代码中实现的功能：

订单号从1401开始自增长
更新订单同时可以更新GOODS表中的数据（通过触发器实现）一共定义了三个触发器，包括对ORDERS表进行增、删、改同时触发修改GOODS表中的数据
实现了使用视图查看具体信息

需要注意的问题：

数据库中的delimiter语句：简单说就是用于定义使用某个符号来结束语句，就像我们默认使用“；”来结束语句，当有时希望执行更多语句，比如定义触发器时，就需要重定义一个符号代替“；”，不需要的时候直接使用”declare ；“ 即可。
到底在什么时候修改表中内容的值？在很多情况下，如果需要修改当前表（触发后才更改的表认为是被触发表）的话，大多时候都是直接使用set new.属性语句，当然此时要使用before update/delete/insert，因为先set再更新就可以成功的修改数值了。
条件语句如何使用

if   then
(执行语句）
else
(执行语句）
end if

上代码：

DROP TABLE IF EXISTS GOODS;
DROP TABLE IF EXISTS ORDERS;

create table goods(
    id int,
    name char(10),
    price int,
    number int,
    primary key(id)
);


create table orders(
    order_id int auto_increment,
    good_id  int,
    number int,
    primary key(order_id)
)
auto_increment = 1401;

delimiter //
create trigger order_good
before insert on orders 
for each row
begin
    if new.number > 20 then
               update goods set goods.number=goods.number - 20 where 

new.good_id = goods.id;
               set new.number = '20';
        else
               update goods set goods.number=goods.number - 

new.number where new.good_id = goods.id;
        end if;
end
//

create trigger cancel_order
after delete on orders
for each row
begin
    update goods set goods.number = (goods.number + old.number) 

where goods.id = old.good_id;
end
//

create trigger change_order
before update on orders
for each row
begin
    if new.number > 20 then
             update goods set goods.number=(goods.number + old.number 

- 20) where new.good_id = goods.id;
             set new.number = 20;
        else
             update goods set goods.number=(goods.number + old.number 

- new.number) where new.good_id = goods.id;
        end if;
end

create trigger 
//
/*
INSERT INTO GOODS VALUES('1001', 'T-Shirt', '20', '1000');

INSERT INTO GOODS VALUES('1002', 'Mobile-pho', '1000', '20');

INSERT INTO GOODS VALUES('1003', 'Cup', '10', '800');

INSERT INTO ORDERS(GOOD_ID, NUMBER) VALUES('1001','100');
INSERT INTO ORDERS(GOOD_ID, NUMBER) VALUES('1002','10');
DELETE FROM ORDERS WHERE ORDERS.ORDER_ID = '1402';
UPDATE ORDERS SET ORDERS.NUMBER = 10 WHERE ORDERS.ORDER_ID = '1401';
*/

#创建视图：
create or replace view company(order_id, good_id, name, number)
as select orders.order_id, orders.good_id, goods.name, orders.number
from orders, goods
where orders.good_id = goods.id;
#展示视图：
select * from company;

create or replace view merchant(order_id,good_id, name, trade_amount)
as select orders.order_id, orders.good_id, goods.name, 

goods.price*orders.number
from orders, goods
where orders.good_id = goods.id;