首页澳门新葡亰官方网站 › Mysql这个事儿之(十黄金时代卡塔尔(英语:State of Qatar)触发器二

Mysql这个事儿之(十黄金时代卡塔尔(英语:State of Qatar)触发器二

Mysql那些事儿之(十一)触发器二

1.
创建一个触发器,表中的行在任何时候被插入或更新时,当前用户名和时间也会被标记在该行中。并且它会检查雇员的姓名以及薪水。

oracle数据库支持物化视图--不是基于基表的虚表,而是根据表实际存在的实表,即物化视图的数据存储在非易失的存储设备上。
下面实验创建ON COMMIT 的FAST刷新模式,在mysql中用触发器实现insert ,
update , delete 刷新操作 1、基础表创建,Orders
表为基表,Order_mv为物化视图表 复制代码
代码如下: mysql> create table Orders( -> order_id int not null
auto_increment, -> product_name varcharnot null, -> price
decimal not null , -> amount smallint not null , -> primary key ;
Query OK, 0 rows affected mysql> create table Order_mv( ->
product_name varchar not null, -> price_sum decimal not null, ->
amount_sum int not null, -> price_avg float not null, ->
order_cnt int not null, -> unique index; Query OK, 0 rows affected
2、insert触发器 复制代码 代码如下:
delimiter $$ create trigger tgr_Orders_insert after insert on Orders
for each row begin set @old_price_sum=0; set @old_amount_sum=0; set
@old_price_avg=0; set @old_orders_cnt=0; select
ifnull,ifnull,ifnull,ifnull from Order_mv where
product_name=new.product_name into
@old_price_sum,@old_amount_sum,@old_price_avg,@old_orders_cnt;
set @new_price_sum=@old_price_sum+new.price; set
@new_amount_sum=@old_amount_sum+new.amount; set
@new_orders_cnt=@old_orders_cnt+1; set
@new_price_avg=@new_price_sum/@new_orders_cnt; replace into
Order_mv
values(new.product_name,@new_price_sum,@new_amount_sum,@new_price_avg,@new_orders_cnt);
end; $$ delimiter ; 3、update触发器 复制代码 代码如下: delimiter $$ create trigger
tgr_Orders_update before update on Orders for each row begin set
@old_price_sum=0; set @old_amount_sum=0; set @old_price_avg=0; set
@old_orders_cnt=0; set @cur_price=0; set @cur_amount=0; select
price,amount from Orders where order_id=new.order_id into
@cur_price,@cur_amount; select ifnull,ifnull,ifnull,ifnull from
Order_mv where product_name=new.product_name into
@old_price_sum,@old_amount_sum,@old_price_avg,@old_orders_cnt;
set @new_price_sum=@old_price_sum-@cur_price+new.price; set
@new_amount_sum=@old_amount_sum-@cur_amount+new.amount; set
@new_orders_cnt=@old_orders_cnt; set
@new_price_avg=@new_price_sum/@new_orders_cnt; replace into
Order_mv
values(new.product_name,@new_price_sum,@new_amount_sum,@new_price_avg,@new_orders_cnt);
end; $$ delimiter ; 4、delete触发器 复制代码 代码如下: delimiter $$ create trigger
tgr_Orders_delete after delete on Orders for each row begin set
@old_price_sum=0; set @old_amount_sum=0; set @old_price_avg=0; set
@old_orders_cnt=0; set @cur_price=0; set @cur_amount=0; select
price,amount from Orders where order_id=old.order_id into
@cur_price,@cur_amount; select ifnull,ifnull,ifnull,ifnull from
Order_mv where product_name=old.product_name into
@old_price_sum,@old_amount_sum,@old_price_avg,@old_orders_cnt;
set @new_price_sum=@old_price_sum - old.price; set
@new_amount_sum=@old_amount_sum - old.amount; set
@new_orders_cnt=@old_orders_cnt - 1; if @new_orders_cnt>0 then
set @new_price_avg=@new_price_sum/@new_orders_cnt; replace into
Order_mv
values(old.product_name,@new_price_sum,@new_amount_sum,@new_price_avg,@new_orders_cnt);
else delete from Order_mv where product_name=@old.name; end if; end;
$$ delimiter ;
5、这里delete触发器有一个bug,就是在一种产品的最后一个订单被删除的时候,Order_mv表的更新不能实现,不知道这算不算是mysql的一个bug。当然,如果这个也可以直接用sql语句生成数据,而导致的直接后果就是执行效率低。
复制代码 代码如下: -> insert into
Order_mv -> select product_name ,sum,avg from Orders -> group by
product_name;

 

--创建测试表
CREATE TABLE emp (
    empname text,
    salary integer,
    last_date timestamp,
    last_user text
);

--创建触发器函数
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
        -- 检查 empname 以及 salary
        IF NEW.empname IS NULL THEN
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary IS NULL THEN
            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
        END IF;

        -- 谁会倒贴钱为我们工作?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- 记住谁在什么时候改变了工资单
        NEW.last_date := current_timestamp;
        NEW.last_user := current_user;
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

--创建触发器
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

--测试触发器
test=# insert into emp values ('John');  --salary为空,触发器报错
ERROR:  John cannot have null salary
CONTEXT:  PL/pgSQL function emp_stamp() line 7 at RAISE

test=# insert into emp values (null,1200);   --empname为空,触发器报错
ERROR:  empname cannot be null
CONTEXT:  PL/pgSQL function emp_stamp() line 4 at RAISE

test=# insert into emp values ('John',-200); --salary为负数,触发器报错
ERROR:  John cannot have a negative salary
CONTEXT:  PL/pgSQL function emp_stamp() line 10 at RAISE

test=# insert into emp values ('Bob',1200);  --成功插入正常数据,并记录了最后操作时间和操作用户
INSERT 0 1
test=# select * from emp;
 empname | salary |         last_date          | last_user 
---------+--------+----------------------------+-----------
 Bob     |   1200 | 2017-08-09 17:39:23.671957 | postgres
(1 row)

相关链接:

  1. 用于审计的触发器过程
    这个例子触发器保证了在emp表上的任何插入、更新或删除一行的动作都被记录(即审计)在emp_audit表中。当前时间和用户名以及在其上执行的操作类型都会被记录到行中。

    --创建测试表
    create table emp (
    empname text not null,
    salary integer
    );

    --创建审计表
    create table emp_audit(
    operation char(1) not null,
    stamp timestamp not null,
    userid text not null,
    empname text not null,
    salary integer
    );

    --创建触发器函数
    create or replace function process_emp_audit() returns trigger as $emp_audit$
    begin

    if (TG_OP = 'DELETE') then
      insert into emp_audit select 'D',now(),user,old.*;
      return old;
    elsif (TG_OP = 'UPDATE') then
      insert into emp_audit select 'U',now(),user,new.*;
      return new;
    elsif (TG_OP = 'INSERT') then
      insert into emp_audit select 'I',now(),user,new.*;
      return new;
    end if;
    return null;
    

    end;
    $emp_audit$ language plpgsql;

    --创建触发器
    create trigger emp_audit
    after insert or update or delete on emp
    for each row execute procedure process_emp_audit();

    --测试触发器
    test=# insert into emp values ('John',1200);
    INSERT 0 1
    test=# select * from emp_audit;
    operation | stamp | userid | empname | salary
    -----------+----------------------------+----------+---------+--------
    I | 2017-08-09 18:18:10.189772 | postgres | John | 1200
    (1 row)

Mysql那些事儿之(一)mysql的安装

 

The
End!

Mysql那些事儿之(二)有关数据库的操作

2017-08-17

Mysql那些事儿之(三)有关数据表的操作

Mysql那些事儿之(四)数据表数据查询操作

Mysql那些事儿之(五)操作时间

;

Mysql那些事儿之(六)字符串模式匹配

;

Mysql那些事儿之(七)深入select查询

;

Mysql那些事儿之(八)索引

Mysql那些事儿之(九)常用的函数

Mysql那些事儿之(十)触发器一

 

比较after insert、before insert、after update、before
update触发时间与事件 的触发情况。

   www.2cto.com  

Sql代码  

--创建表  

create table film_text(  

id smallint auto_increment,  

name varchar(40),  

txt text,  

primary key(id)  

);  

  

对于film表在 触发器 一 里有提过,代码就不写了。

针对表film 创建before insert 触发器:

 

Sql代码  

--创建before insert 触发器  

create trigger trigger_film_bef  --触发器名称为trigger_film_bef   

转载本站文章请注明出处:澳门新葡亰官方网站 http://www.radioritmo-bl.com/?p=570

上一篇:

下一篇:

相关文章