Oracle学习笔记之触发器

一。触发器实现 crud两表同步。

建表：

create table t1 (
   ID                   INTEGER                        not null,
   UserName            VARCHAR(30)                     not null,
   PassWord            VARCHAR(20)                     not null,
   constraint PK_User_Info primary key (ID)
);

create table t2 (
   ID                   INTEGER                         not null,
   UserName            VARCHAR(30)                     not null,
   Email            VARCHAR(20)                     not null,
   Phone            VARCHAR(20)                     not null,
   constraint PK_User_Info_temp primary key (ID)
);

触发器写法：

create or replace trigger tri_t1_t2 after insert or update or delete
on t1 for each row
declare
   empno_remaining EXCEPTION;
   PRAGMA EXCEPTION_INIT(empno_remaining, -1);
   /* -1 是违反唯一约束条件的错误代码 * 
begin
if inserting then
    insert into t2 values(:NEW.ID,:NEW.UserName,:NEW.PassWord);
elsif updating then
    update t2 set ID=:NEW.ID,UserName=:NEW.UserName where id=:OLD.id;
elsif deleting then
    delete from t2 where id=:OLD.id;
end if;
exception
  WHEN empno_remaining THEN
      DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');
  WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE|| '---' ||SQLERRM);
      //SQLCODE 和SQLERRM 是两个Oracle函数，返回异常信息。或者用下面的方法来处理（服务器返回异常在客户端显示）
      //raise_application_error(-20001, '是另外的错误!');
      //注意此方法的用法：第一个参数-20000~-20999，第二个自定义字符串（2k以内）。
end;

 

2。插入一条记录，这条数据要作为判断能否插入的条件，就要用到临时表。
（例如往一个表中插入百分比，要满足所有数据的百分比之和小于100）
如下例：要求插入单项成绩权重和小于100：
(1).临时表：tmp
create global temporary table tmp(STDSCOREDETAIL_ID VARCHAR2(32),
DXMC VARCHAR2(20),DXQZ NUMBER(5,2),DXDF VARCHAR2(10),SCS_ID VARCHAR2(32),DXCJ NUMBER(5,2))
on commit delete rows;

 

(2).触发器:tri_jw_stdscoredetail_1
create or replace trigger tri_jw_stdscoredetail_1 before insert or update
on jw_stdscoredetail
for each row

 

Declare
qz Number:=0;

 

begin
     Insert into tmp values(:new.STDSCOREDETAIL_ID,:new.DXMC,:new.DXQZ,:new.DXDF,:new.SCS_ID,:new.DXCJ);
     select sum(t.dxqz) into qz from tmp t where t.scs_id=:new.SCS_ID;
     if(qz>100) then
        Raise_application_error(-20001,'数据出错，保存数据时单项权重总和大于100%');
     end if;
    
end;

2。插入一条记录，这条数据要作为判断能否插入的条件，就要用到临时表。
（例如往一个表中插入百分比，要满足所有数据的百分比之和小于100）
如下例：要求插入单项成绩权重和小于100：
(1).临时表：tmp
create global temporary table tmp(STDSCOREDETAIL_ID VARCHAR2(32),
DXMC VARCHAR2(20),DXQZ NUMBER(5,2),DXDF VARCHAR2(10),SCS_ID VARCHAR2(32),DXCJ NUMBER(5,2))
on commit delete rows;

(2).触发器:tri_jw_stdscoredetail_1
create or replace trigger tri_jw_stdscoredetail_1 before insert or update
on jw_stdscoredetail
for each row

Declare
qz Number:=0;

begin
     Insert into tmp values(:new.STDSCOREDETAIL_ID,:new.DXMC,:new.DXQZ,:new.DXDF,:new.SCS_ID,:new.DXCJ);
     select sum(t.dxqz) into qz from tmp t where t.scs_id=:new.SCS_ID;
     if(qz>100) then
        Raise_application_error(-20001,'数据出错，保存数据时单项权重总和大于100%');
     end if;
    
end;