Oracle触发器使用

原创 2015年11月19日 09:36:30

触发器(trigger)

        应用场景:1.复杂的安全性检查;2.数据确认;3.实现审计功能;4.完成数据的备份和同步。
        类型:语句级触发器(针对是表)和行级触发器(针对是行, :old和:new表示一行的记录,其中:old表示操作之前的值,:new表示操作之后的值)。

基本结构

create or replace trigger 触发器名称
after【or before】 insert 【or delete or update[列名]】
on 表名称
for each row when(条件)
declare
begin
    dbms_output.put_line('触发器已经执行!'); // PL/SQL语句块
end;

案例一(非工作时间禁止插入数据,针对应用场景1、2、3原理都一样):
create or replace 
trigger SECURITYEMP 
BEFORE INSERT ON BAR3D 
BEGIN
 if to_char(sysdate,'day') in('星期六','星期日') or
    to_number(to_char(sysdate,'hh24')) not between 9 and 18 then
 raise_application_error(-20001, '禁止在非工作时间插入数据');
 end if;
END;

案例二(将子表汇总数据添加到主表中,针对应用场景4,数据备份与同步原理一样)
create or replace 
trigger "SCOTT"."CAPACITY_SUM" 
AFTER INSERT OR UPDATE ON "SCOTT"."MACHINE_UNIT_INFO"
for each row
declare 
PRAGMA AUTONOMOUS_TRANSACTION;
capa NUMBER(10,2) := 10000;
BEGIN
SELECT CAPACITY into capa  FROM VIEW_SECURITY_ORGANIZATION where CODE=:new.FACTORYCODE;
UPDATE SECURITY_ORGANIZATION  SET CAPACITY=capa  WHERE CODE=:new.FACTORYCODE;
COMMIT;
END;

附表结构:
表SECURITY_ORGANIZATION
/*
Navicat Oracle Data Transfer
Oracle Client Version : 10.2.0.5.0

Source Server         : 192.168.1.201ORCL
Source Server Version : 100200
Source Host           : 192.168.1.201:1521
Source Schema         : SCOTT

Target Server Type    : ORACLE
Target Server Version : 100200
File Encoding         : 65001

Date: 2015-11-19 09:40:25
*/


-- ----------------------------
-- Table structure for SECURITY_ORGANIZATION
-- ----------------------------
DROP TABLE "SCOTT"."SECURITY_ORGANIZATION";
CREATE TABLE "SCOTT"."SECURITY_ORGANIZATION" (
"ORG_ID" NUMBER NOT NULL ,
"NAME" VARCHAR2(64 BYTE) NOT NULL ,
"DESCRIPTION" VARCHAR2(255 BYTE) NULL ,
"PARENT_ID" NUMBER NULL ,
"CODE" VARCHAR2(20 BYTE) NULL ,
"CAPACITY" NUMBER(10) NULL ,
"LONGITUDE" NUMBER(20,10) NULL ,
"LATITUDE" NUMBER(20,10) NULL ,
"ADDRESS" VARCHAR2(150 BYTE) NULL ,
"HOTAREA" NUMBER(10,2) NULL ,
"HOTWAYS" VARCHAR2(100 BYTE) NULL ,
"MINRUNWAYS" NUMBER(10,2) NULL ,
"DJJDATA" NUMBER(10,2) NULL ,
"JCDATA" NUMBER(10,2) NULL ,
"ISAPPLYHOT" CHAR(1 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;
COMMENT ON TABLE "SCOTT"."SECURITY_ORGANIZATION" IS '组织结构';
COMMENT ON COLUMN "SCOTT"."SECURITY_ORGANIZATION"."NAME" IS '组织名称';
COMMENT ON COLUMN "SCOTT"."SECURITY_ORGANIZATION"."DESCRIPTION" IS '组织名称描述';
COMMENT ON COLUMN "SCOTT"."SECURITY_ORGANIZATION"."CODE" IS '组织编码';
COMMENT ON COLUMN "SCOTT"."SECURITY_ORGANIZATION"."CAPACITY" IS '总装机容量(MW)';
COMMENT ON COLUMN "SCOTT"."SECURITY_ORGANIZATION"."LONGITUDE" IS '经度';
COMMENT ON COLUMN "SCOTT"."SECURITY_ORGANIZATION"."LATITUDE" IS '纬度';
COMMENT ON COLUMN "SCOTT"."SECURITY_ORGANIZATION"."ADDRESS" IS '地点';
COMMENT ON COLUMN "SCOTT"."SECURITY_ORGANIZATION"."HOTAREA" IS '供热面积';
COMMENT ON COLUMN "SCOTT"."SECURITY_ORGANIZATION"."HOTWAYS" IS '供热方式';
COMMENT ON COLUMN "SCOTT"."SECURITY_ORGANIZATION"."MINRUNWAYS" IS '最小运行方式';
COMMENT ON COLUMN "SCOTT"."SECURITY_ORGANIZATION"."DJJDATA" IS '电监局数据';
COMMENT ON COLUMN "SCOTT"."SECURITY_ORGANIZATION"."JCDATA" IS '机测数据';
COMMENT ON COLUMN "SCOTT"."SECURITY_ORGANIZATION"."ISAPPLYHOT" IS 'Y供热?N不供热';

-- ----------------------------
-- Indexes structure for table SECURITY_ORGANIZATION
-- ----------------------------

-- ----------------------------
-- Checks structure for table SECURITY_ORGANIZATION
-- ----------------------------
ALTER TABLE "SCOTT"."SECURITY_ORGANIZATION" ADD CHECK ("ORG_ID" IS NOT NULL);
ALTER TABLE "SCOTT"."SECURITY_ORGANIZATION" ADD CHECK ("NAME" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table SECURITY_ORGANIZATION
-- ----------------------------
ALTER TABLE "SCOTT"."SECURITY_ORGANIZATION" ADD PRIMARY KEY ("ORG_ID");

<span style="font-family: 微软雅黑; font-size: 14px; line-height: 21px;">表</span>MACHINE_UNIT_INFO
/*
Navicat Oracle Data Transfer
Oracle Client Version : 10.2.0.5.0

Source Server         : 192.168.1.201ORCL
Source Server Version : 100200
Source Host           : 192.168.1.201:1521
Source Schema         : SCOTT

Target Server Type    : ORACLE
Target Server Version : 100200
File Encoding         : 65001

Date: 2015-11-19 09:40:42
*/


-- ----------------------------
-- Table structure for MACHINE_UNIT_INFO
-- ----------------------------
DROP TABLE "SCOTT"."MACHINE_UNIT_INFO";
CREATE TABLE "SCOTT"."MACHINE_UNIT_INFO" (
"ID" NUMBER NOT NULL ,
"FACTORYCODE" VARCHAR2(50 BYTE) NULL ,
"UNITCODE" VARCHAR2(50 BYTE) NULL ,
"UNITNAME" VARCHAR2(100 BYTE) NULL ,
"DESCRIPTION" VARCHAR2(255 BYTE) NULL ,
"CAPACITY" NUMBER(10,2) NULL ,
"TLGY" VARCHAR2(100 BYTE) NULL ,
"TXGY" VARCHAR2(100 BYTE) NULL ,
"CCGY" VARCHAR2(100 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;
COMMENT ON COLUMN "SCOTT"."MACHINE_UNIT_INFO"."FACTORYCODE" IS '风场编码';
COMMENT ON COLUMN "SCOTT"."MACHINE_UNIT_INFO"."UNITCODE" IS '机组编码';
COMMENT ON COLUMN "SCOTT"."MACHINE_UNIT_INFO"."UNITNAME" IS '机组名称';
COMMENT ON COLUMN "SCOTT"."MACHINE_UNIT_INFO"."DESCRIPTION" IS '机组描述';
COMMENT ON COLUMN "SCOTT"."MACHINE_UNIT_INFO"."CAPACITY" IS '装机容量(MW)';
COMMENT ON COLUMN "SCOTT"."MACHINE_UNIT_INFO"."TLGY" IS '脱硫工艺';
COMMENT ON COLUMN "SCOTT"."MACHINE_UNIT_INFO"."TXGY" IS '脱销工艺';
COMMENT ON COLUMN "SCOTT"."MACHINE_UNIT_INFO"."CCGY" IS '除尘工艺';

-- ----------------------------
-- Indexes structure for table MACHINE_UNIT_INFO
-- ----------------------------

-- ----------------------------
-- Triggers structure for table MACHINE_UNIT_INFO
-- ----------------------------
CREATE OR REPLACE TRIGGER "SCOTT"."CAPACITY_SUM" AFTER INSERT OR UPDATE ON "SCOTT"."MACHINE_UNIT_INFO" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
declare 
  PRAGMA AUTONOMOUS_TRANSACTION;
  capa NUMBER(10,2) := 0;
BEGIN
  SELECT CAPACITY into capa  FROM VIEW_SECURITY_ORGANIZATION where CODE=:new.FACTORYCODE;
  UPDATE SECURITY_ORGANIZATION  SET CAPACITY=capa  WHERE CODE=:new.FACTORYCODE;
  COMMIT;
END;
-- ----------------------------
-- Checks structure for table MACHINE_UNIT_INFO
-- ----------------------------
ALTER TABLE "SCOTT"."MACHINE_UNIT_INFO" ADD CHECK ("ID" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table MACHINE_UNIT_INFO
-- ----------------------------
ALTER TABLE "SCOTT"."MACHINE_UNIT_INFO" ADD PRIMARY KEY ("ID");
视图VIEW_SECURITY_ORGANIZATION
SELECT
	A .ORG_ID,
	A ."NAME",
	A ."DESCRIPTION",
	A .PARENT_ID,
	A .CODE,
	A .LONGITUDE,
	A .LATITUDE,
	A .ADDRESS,
	A .HOTAREA,
	A .HOTWAYS,
	A .MINRUNWAYS,
	A .DJJDATA,
	A .JCDATA,
	A .ISAPPLYHOT,
	b.CAPACITY
FROM
	SECURITY_ORGANIZATION A --装机总容量计算
LEFT JOIN (
	SELECT
		SUM (CAPACITY) CAPACITY,
		FACTORYCODE
	FROM
		MACHINE_UNIT_INFO
	GROUP BY
		FACTORYCODE
) b ON A .CODE = b.FACTORYCODE --WHERE CODE NOT LIKE '%.%.%' OR CODE IS NULL










版权声明:本文为博主原创文章,未经博主允许不得转载。

Oracle中循环语句的触发器

循环语句--loop  是plsql中最简单的循环语句,这种循环语句以loop开头,以end loop 结尾,这种循环至少会被执行一次 就是说先循环一次在去判断 案例: 请编写一个过程,可输入...
  • c622724
  • c622724
  • 2012年12月18日 23:02
  • 1625

Oracle 触发器中使用游标

为表ANNOUNCEMENT创建插入触发器,原先设计的库是先将选择的所有人员插入到ANNOUNCEMENTORG表中,然后在ANNOUNCEMENT表中插入其他相关信息。表ANNOUNCEMENTOR...
  • taomanman
  • taomanman
  • 2014年12月26日 11:02
  • 2641

oracle创建视图触发器

1.视图触发器语法 2.视图触发器作用 3.视图触发器实例
  • baidu_37107022
  • baidu_37107022
  • 2017年07月08日 00:01
  • 650

关于使用navicat创建oracle触发器

今天在使用navicat创建触发器的时候,遇到一个问题。 我之前创建了一个update触发器, CREATE OR REPLACE TRIGGER TRIG_UPDATE AFTER UPDAT...
  • gm799651127
  • gm799651127
  • 2015年11月28日 16:38
  • 1202

PL/SQL之触发器谓词

触发器谓词INSERTING,UPDATING,DELETING
  • yhl_jxy
  • yhl_jxy
  • 2016年03月01日 11:20
  • 1032

oracle 里的函数调用问题和触发器,函数包的问题

当函数没有参数时,小括号可以省略! create or replace function sal_tax(v_sal number) return number is begin if(v_...
  • yangnuowei88
  • yangnuowei88
  • 2013年03月09日 21:25
  • 1531

oracle For循环和触发器Tigger

--循环 begin   for cycle in (select id, testcycle from specialpapers) loop     if (cycle.testcycle is ...
  • sensenlin91
  • sensenlin91
  • 2016年04月05日 16:57
  • 1042

oralce用触发器完成级联删除

举例 删除dept其中的一个部门,顺带删除员工表emp中这些部门的员工 dept 表  CREATE TABLE SCOTT.DEPT ( DEPTNO NUMBER(2), ...
  • zj0078
  • zj0078
  • 2012年04月26日 09:00
  • 1938

oracle编程 if, for , while, loop, 存储过程,触发器,游标演示

dept,employee和《oracle常用语句,带详细的说明》中的表一致!   /*作用:sql编程   说明:declare 申明变量,constant 常量,语言块要放在begin end...
  • christine_Ruan
  • christine_Ruan
  • 2012年05月20日 15:09
  • 2811

利用Oracle DDL触发器实现DDL监控

前言创建此触发器的主要目的是为了控制数据库的版本,虽然会将DDL语句保留但难免会出现遗漏,所以创建DDl触发器记录DDL操作,主要是用来核对数据库变更的SQL语句创建用户并授权...
  • jaune161
  • jaune161
  • 2015年08月05日 17:31
  • 3367
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle触发器使用
举报原因:
原因补充:

(最多只允许输入30个字)