PL/pgSQL 可以用于定义触发器过程。 一个触发器过程是用 CREATE FUNCTION 命令创建的, 创建的形式是一个不接受参数并且返回 trigger 类型的函数。 请注意该函数即使在 CREATE TRIGGER 声明里声明为准备接受参数, 它也必需声明为无参数 — 触发器的参数是通过 TG_ARGV 传递的,下面有描述。
在一个 PL/pgSQL 函数当做触发器调用的时候, 系统会在顶层的声明段里自动创建几个特殊变量。有如下这些:
数据类型是 RECORD; 该变量为INSERT/UPDATE 操作时保存行(ROW)一级的触发器新的数据库行。 在语句级别的触发器里,这个变量是 NULL。
数据类型是 RECORD; 该变量为 INSERT/UPDATE 操作时保存行(ROW)一级的触发器新的数据库行。 在语句级别的触发器里,这个变量是 NULL。
数据类型是 name;该变量包含实际触发的触发器名。 fired.
数据类型是 text;是一个由触发器定义决定的字符串, 要么是 BEFORE 要么是 AFTER。
数据类型是 text;是一个由触发器定义决定的字符串, 要么是 ROW 要么是 STATEMENT。
数据类型是 text;是一个说明触发触发器的操作的字符串, 可以是 INSERT,UPDATE 或者 DELETE。
数据类型是 oid;是导致触发器调用的表的对象标识(OID)。
数据类型是 name;是激活触发器调用的表的名称。
数据类型是 integer; 是在CREATE TRIGGER 语句里面赋予触发器过程的参数的个数。
数据类型是 text 的数组;是 CREATE TRIGGER语句里的参数。 下标从 0 开始记数.非法下标(小于 0 或者大于等于 tg_nargs)导致返回一个 NULL 值。
一个触发器函数必须返回 NULL 或者是 一个与导致触发器运行的表的记录/行完全一样的结构的数据。
因BEFORE触发的行级别的的触发器可以返回一个 NULL,告诉触发器管理器忽略对该行剩下的操作 (也就是说,随后的触发器将不再执行,并且不会对该行产生INSERT/UPDATE/DELETE动作)。 如果返回了一个非 NULL 的行,那么将继续对该行数值进行处理。 请注意,返回一个和原来的NEW不同的行数值将修改那个将插入或更新的行。 我们可能用一个值直接代替NEW里的某个数值并且返回之,或者我们也可以构建一个完全新的记录/行再返回。
BEFORE 或者 AFTER语句级别的触发器, 或者一个AFTER 行级别的触发器的返回值将总是被忽略; 它们也可以返回 NULL 来忽略返回值。不过,任何这种类型的触发器仍然可以 通过抛出一个错误来退出整个触发器操作。
Example 36-2 显示了一个 PL/pgSQL 写的触发器过程的例子。
Example 36-2. 一个PL/pgSQL触发器过程
下面的例子触发器的作用是:任何时候表中插入或更新了行, 当前的用户名和时间都记录入行中。 并且它保证给出了雇员名称并且薪水是一个正数。
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 ISNULL THEN RAISE EXCEPTION 'empname cannot be null'; END IF; IF NEW.salary ISNULL 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();
另外一个向表里记录变化的方法涉及创建一个新表,然后为后来发生的每次插入、更新或者删除动作保存一行。 这个方法可以当作对一个表的审计。 Example 36-3 显示了一个 PL/pgSQL 写的审计触发器过程的例子。
Example 36-3. 一个用于审计的 PL/pgSQL 触发器过程
这个例子触发器保证了在 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 -- -- 在 emp_audit 里创建一行,反映对 emp 的操作, -- 使用特殊变量 TG_OP 获取操作类型。 -- 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; END IF; RETURN NULL; -- 忽略结果,因为它是个 AFTER 触发器 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() ;
触发器的一个用途是维持另外一个表的概要。生成的概要可以用于在某些查询中代替原始表 — 通常可以大大缩小运行时间。 这个技巧经常用于数据仓库,这个时候,需要测量的表(叫事实表)可能会非常巨大。 Example 36-4 演示了一个 PL/pgSQL 触发器过程的例子, 它为某个数据仓库的一个事实表维护一个概要表。
Example 36-4. 一个维护概要表的 PL/pgSQL 触发器过程
下面的模式有一部分是基于 Ralph Kimball 的The Data Warehouse Toolkit 里面的 Grocery Store 例子。
-- -- 主表 - 时间维以及销售事实。 -- CREATE TABLE time_dimension ( time_key integer NOT NULL, day_of_week integer NOT NULL, day_of_month integer NOT NULL, month integer NOT NULL, quarter integer NOT NULL, year integer NOT NULL ); CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key); CREATE TABLE sales_fact ( time_key integer NOT NULL, product_key integer NOT NULL, store_key integer NOT NULL, amount_sold numeric(12,2) NOT NULL, units_sold integer NOT NULL, amount_cost numeric(12,2) NOT NULL ); CREATE INDEX sales_fact_time ON sales_fact(time_key); -- -- 摘要表 - 根据时间的销售。 -- CREATE TABLE sales_summary_bytime ( time_key integer NOT NULL, amount_sold numeric(15,2) NOT NULL, units_sold numeric(12) NOT NULL, amount_cost numeric(15,2) NOT NULL ); CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key); -- -- 在 UPDATE,INSERT,DELETE 的时候根新概要字段的函数和触发器。 -- CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS $maint_sales_summary_bytime$ DECLARE delta_time_key integer; delta_amount_sold numeric(15,2); delta_units_sold numeric(12); delta_amount_cost numeric(15,2); BEGIN -- 计算增/减量。 IF (TG_OP = 'DELETE') THEN delta_time_key = OLD.time_key; delta_amount_sold = -1 * OLD.amount_sold; delta_units_sold = -1 * OLD.units_sold; delta_amount_cost = -1 * OLD.amount_cost; ELSIF (TG_OP = 'UPDATE') THEN -- 禁止改变 time_key 的更新 - -- (可能并不是很强制,因为 DELETE + INSERT 是大多数可能 -- 产生的修改)。 IF ( OLD.time_key != NEW.time_key) THEN RAISE EXCEPTION 'Update of time_key : % -> % not allowed', OLD.time_key, NEW.time_key; END IF; delta_time_key = OLD.time_key; delta_amount_sold = NEW.amount_sold - OLD.amount_sold; delta_units_sold = NEW.units_sold - OLD.units_sold; delta_amount_cost = NEW.amount_cost - OLD.amount_cost; ELSIF (TG_OP = 'INSERT') THEN delta_time_key = NEW.time_key; delta_amount_sold = NEW.amount_sold; delta_units_sold = NEW.units_sold; delta_amount_cost = NEW.amount_cost; END IF; -- 用新数值更新概要行。 UPDATE sales_summary_bytime SET amount_sold = amount_sold + delta_amount_sold, units_sold = units_sold + delta_units_sold, amount_cost = amount_cost + delta_amount_cost WHERE time_key = delta_time_key; -- There might have been no row with this time_key (e.g new data!). IF (NOT FOUND) THEN BEGIN INSERT INTO sales_summary_bytime ( time_key, amount_sold, units_sold, amount_cost) VALUES ( delta_time_key, delta_amount_sold, delta_units_sold, delta_amount_cost ); EXCEPTION -- -- 捕获两个事务维一个新 time_key 增加数据的冲突条件 -- WHEN UNIQUE_VIOLATION THEN UPDATE sales_summary_bytime SET amount_sold = amount_sold + delta_amount_sold, units_sold = units_sold + delta_units_sold, amount_cost = amount_cost + delta_amount_cost WHERE time_key = delta_time_key; END; END IF; RETURN NULL; END; $maint_sales_summary_bytime$ LANGUAGE plpgsql; CREATE TRIGGER maint_sales_summary_bytime AFTER INSERT OR UPDATE OR DELETE ON sales_fact FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();
发表评论
-
Oracle 还原序列
2011-05-23 00:07 887declare n number(10); tsql ... -
instantclient_10_2客户端配置
2011-05-23 00:07 22771.下载Oracle Client Package . ... -
Oracle分页过程
2011-05-23 00:03 639CREATE OR REPLACE PROCEDURE &q ... -
手工创建Oracle数据库
2011-05-23 00:01 749系统环境: 1、操作系统:Windows 2000 Serv ... -
oracle常用命令
2011-05-22 23:59 501如何单独备份一个或多个用户: D:\>exp sco ... -
有效创建Oracle dblink的两种方式
2011-05-22 23:59 737两台不同的数据库服务器,从一台数据库服务器的一个用户读取另一台 ... -
oracle 绑定变量(bind variable)
2011-05-22 23:58 975绑定变量 在oracle 中,对于一个提交的sql ... -
oracle exp/imp命令详解
2011-05-22 23:56 702E:\>exp help=y 通过输入 EXP 命 ... -
Oracle备份与恢复
2011-05-22 23:54 637Oracle备份与恢复:逻辑备份 [导出(Export)导 ... -
一些常用PLSQL语句 和事务
2011-05-22 23:52 1102基本的 PL/SQL语句AS 改变标题:列名 AS 列标题 ... -
学习ORACLE 视图
2011-05-22 23:50 614--视图--视图是一个逻辑表,是查看表的一种方式,用来定义来 ... -
学习ORCALE索引
2011-05-22 23:49 699--索引--索引是加快检索表中数据的方式。对于包含大量数据的表 ... -
学习ORCALE 表和约束
2011-05-22 23:48 561--表的建立create table TEST(ID nu ... -
学习ORCALE表空间、用户建立SQL
2011-05-22 23:47 843CREATE tablespace MYTEST DATAFI ... -
Oracle数据库的体系结构
2011-05-21 14:31 5261. 物理结构 物理数据库结构是由构成数据库的操作系统文 ... -
ORACLE数据库的模式对象的管理与维护
2011-05-21 14:22 742一、ORACLE数据库的模式对象的管理与维护 本节的主要 ...
相关推荐
数据库 存储过程 触发器 基本简介
存储过程和触发器详解 存储过程和触发器详解 存储过程和触发器详解存储过程和触发器详解
实验5.1 存储过程的建立与使用 一、实验目的 理解存储过程的概念、作用、建立和调用方法。 二、实验原理 使用CREATE PROCEDURE语句创建存储过程,ALTER PROCEDURE语句修改存储过程,DROP PROCEDURE语句删除存储过程...
触发器调用存储过程
mysql触发器+存储过程
可以定义一个无论何时用INSERT语句向表中插入数据时都会执行的触发器。 当触发INSERT触发器时,新的数据行就会被插入到触发器表和inserted表中。inserted表是一个逻辑表,它包含了已经插入的数据行的一个副本。...
一、实验目的 1.了解存储过程的概念、优点 2.熟练掌握创建存储过程的方法 3.熟练掌握存储过程的调用方法 4.了解触发器的概念、优点 5.掌握触发器的方法和步骤 6.掌握触发器的使用
创建一个名为stu_pr的存储过程,该存储过程能查询出051班学生的所有资料,包括学生的基本信息、学生的选课信息(含未选课同学的信息)。要求在创建存储过程前请判断该存储过程是否已创建,若已创建则先删除,并给出...
使学生加深对触发器和存储过程的创建和使用。 二、实验内容和要求 掌握触发器的使用。 掌握存储过程的创建、修改和删除;掌握存储过程的执行。 具体内容如下: (1)触发器的使用 在数据表“学生”中创建...
存储过程触发器 游标是数据库中的一个重要触发器,该篇讲述触发器详细,偏于广大学者学习参考。
存储过程or触发器.txt存储过程or触发器.txt存储过程or触发器.txt存储过程or存储过程or触发器.txt触发器.txt存储过程or触发器.txt存储过程or触发器.txt存储过程or触发器.txt
存储过程与触发器存储过程与触发器存储过程与触发器存储过程与触发器
存储过程、函数、触发器和包
存储过程的创建和触发器的创建和查询、删除以及应用
存储过程和触发器的概念,创建和使用过程,代码样例
Mysql存储过程游标触发器
SQL2000相关的存储过程和触发器讲解ppt
1.了解存储过程的功能特点 2.掌握 SQL Server 创建和管理存储过程的方法,存储过程的参数定义和存储过程的执行 2.掌握 SQLServer 触发器的
图书管理系统触发器和存储过程.pdf
oracle触发器与存储过程高级编程