oracle触发器

1、简单插入测试

测试表：

CREATE TABLE  TEST1
   ( ID  NUMBER(*,0),
"NAME   VARCHAR2(40)）；

创建触发器：

 create or replace trigger trig_tbtest
before insert on test1 for each row
declare
v_count number(5);
begin
select count(*) into v_count from test1 where name=:new.name;
if (v_count>0) then
    raise_application_error(-20000,'data is duplication');
end if;
end;

执行：

SQL> select * from test1;
 
                                     ID NAME
--------------------------------------- ----------------------------------------
                                      1 aa
                                      2 bb
                                      3 aa
 
SQL> insert into test1 values(4,'aa');
 
insert into test1 values(4,'aa')
 
ORA-20000: data is duplication
ORA-06512: 在 "SHIHUA.TRIG_TBTEST", line 6
ORA-04088: 触发器 'SHIHUA.TRIG_TBTEST' 执行过程中出错
 
SQL> insert into test1 values(4,'ww');
 
1 row inserted

 

一个实用例子：

功能插入或者修改数据时候更新表中一列，列的value从另一个表中查询

CREATE OR REPLACE TRIGGER TRI_DBA_OBJECTS1
  BEFORE INSERT OR UPDATE -- OR DELETE
ON DBA_OBJECTS1
  FOR EACH ROW
DECLARE
  TYPE1 VARCHAR2(19);
BEGIN
  IF INSERTING THEN
    -- DBMS_OUTPUT.PUT_LINE('执行了UPDATA操作');
    -- 从另一个表中查询列，赋值变量
    SELECT MAX(DPOLL)
      INTO TYPE1
      FROM DUAL, T_ETLE_MAP TB
     WHERE TB.SPOLL = :NEW.OBJECT_TYPE;
     -- 非空则进行替换
     IF TYPE1 IS NOT NULL THEN
      :NEW.OBJECT_TYPE := TYPE1;
      END IF;
     ELSIF UPDATING THEN
    -- DBMS_OUTPUT.PUT_LINE('执行了UPDATA操作');
     SELECT MAX(DPOLL)
      INTO TYPE1
      FROM DUAL, T_ETL_MAP TB
     WHERE TB.SPOLL = :OLD.OBJECT_TYPE;
     IF TYPE1 IS NOT NULL THEN
      :NEW.OBJECT_TYPE := TYPE1;
      END IF;
    
    /* ELSIF DELETING THEN
    DBMS_OUTPUT.PUT_LINE('执行了DELETE操作');*/
  END IF;
END;