mysql数据库和postgreSql数据库行级触发器常用操作

51 阅读 0 评论 34 点赞

我是靠谱客的博主美好早晨，最近开发中收集的这篇文章主要介绍mysql数据库和postgreSql数据库行级触发器常用操作，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

在各个数据库中我们经常会对数据进行备份操作,可以借助触发器来实现

emp为数据操作表

emp_audit理解为数据备份表

mysql行级别触发器(mysql非常简单):

创建表emp:
CREATE TABLE emp (
    empname           text NOT NULL,
    salary            integer
);
创建表emp_audit:
CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary integer
);
-- 创建emp新增数据时候的触发器,把插入的这条新数据备份到emp_audit表
CREATE  TRIGGER `insert_tigger` AFTER INSERT ON `emp` FOR EACH ROW BEGIN
  INSERT INTO emp_audit SELECT 'INSERT', now(), USER(), new.empname,new.salary;
 END;
-- 创建emp更新数据时候的触发器,把修改的这条新数据备份到emp_audit表
CREATE  TRIGGER `update_tigger` AFTER UPDATE ON `emp` FOR EACH ROW BEGIN
  INSERT INTO emp_audit SELECT 'update', now(), USER(), new.empname,new.salary;
 END;
-- 创建emp删除数据时候的触发器,把删除的哪条老数据备份到emp_audit表
CREATE  TRIGGER `del_tigger` AFTER DELETE ON `emp` FOR EACH ROW BEGIN
  INSERT INTO emp_audit SELECT 'del', now(), USER(), OLD.empname,OLD.salary;
 END;

postgresql行级别触发器(稍微需要多写点了,借助FUNCTION函数来进行):

创建表emp:
CREATE TABLE emp (
    empname           text NOT NULL,
    salary            integer
);
创建表emp_audit:
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
        -- 在emp_audit中创建一行以反映在emp上执行的操作,
        -- 使用特殊的变量TG_OP来计算这个操作.
        IF (TG_OP = 'DELETE') THEN --emp表执行删除操作时候,把删除的这条数据放到emp_audit表(OLD.*表示旧数据,NEW.*表示新数据)
            INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN --emp表执行修改操作时候,把修改的这条新数据放到emp_audit表
            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN --emp表执行插入操作时候,把插入的的这条新数据放到emp_audit表
            INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;

为表emp添加触发器,然后执行的函数
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp --在emp_audit表每一行操作之后,执行增删查改是触发
    FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();--执行的函数