oracle触发器 记录 dml,oracle DML触发器

语法请直接百度。

搭建测试数据：

CREATE TABLE TB_USER

(

ID INTEGER PRIMARY KEY,

USER_NAME VARCHAR2(20) NOT NULL,

USER_AGE INTEGER NOT NULL

);

CREATE SEQUENCE SEQ_USER

INCREMENT BY 1

START WITH 1

NOMAXVALUE

NOCYCLE

CACHE 10;

CREATE OR REPLACE TRIGGER TR_USER BEFORE INSERT ON TB_USER FOR EACH ROW

BEGIN

SELECT SEQ_USER.NEXTVAL INTO :NEW.ID FROM DUAL;

END;

触发器1需要用到的表：

CREATE TABLE TB_USER_COPY

(

USER_NAME VARCHAR2(20) NOT NULL,

USER_AGE INTEGER NOT NULL

);

触发器1：

create or replace trigger tri_user_copy

before insert on tb_user for each row

begin

:new.user_name := 'tri_' || :new.user_name;

insert into tb_user_copy values (:new.user_name, :new.user_age);

end;

测试1：

DECLARE

V_AGE TB_USER.USER_AGE%TYPE;

V_NAME TB_USER.USER_NAME%TYPE;

BEGIN

FOR I IN 1..10 LOOP

SELECT DBMS_RANDOM.value(1, 100) INTO V_AGE FROM DUAL;

V_NAME := 'USER_' || V_AGE;

INSERT INTO TB_USER(USER_NAME, USER_AGE) VALUES (V_NAME, V_AGE);

END LOOP;

COMMIT;

END;

触发器2需要用到的表：

CREATE TABLE TB_USER_HIS

(

OLD_USER_NAME VARCHAR2(20) NOT NULL,

OLD_USER_AGE INTEGER NOT NULL,

NEW_USER_NAME VARCHAR2(20) NOT NULL,

NEW_USER_AGE INTEGER NOT NULL

);

触发器2：

create or replace trigger tri_user_his

after update on tb_user for each row

begin

insert into tb_user_his values (:old.user_name, :old.user_age, :new.user_name, :new.user_age);

end;

测试2：

DECLARE

CURSOR C_UPDATE IS SELECT USER_NAME, USER_AGE FROM TB_USER FOR UPDATE;

V_ROW C_UPDATE%ROWTYPE;

V_NAME TB_USER.USER_NAME%TYPE;

V_AGE TB_USER.USER_AGE%TYPE;

BEGIN

FOR V_ROW IN C_UPDATE LOOP

V_NAME := V_ROW.USER_NAME || '_update';

V_AGE := V_ROW.USER_AGE + 1;

UPDATE TB_USER SET USER_NAME=V_NAME, USER_AGE=V_AGE WHERE CURRENT OF C_UPDATE;

END LOOP;

COMMIT;

END;

触发器3需要用到的表：

CREATE TABLE TB_USER_DEL

(

USER_NAME VARCHAR2(20) NOT NULL,

USER_AGE INTEGER NOT NULL

);

触发器3：

create or replace trigger tri_user_del

before delete on tb_user for each row

begin

insert into tb_user_del values (:old.user_name, :old.user_age);

end;

测试3：

delete from tb_user where id=1;

commit;

触发器4需要用到的表：

CREATE TABLE TB_USER_LOG

(

ID INTEGER PRIMARY KEY,

ACTION VARCHAR2(20) NOT NULL,

ACTION_TIME TIMESTAMP NOT NULL,

USER_NAME VARCHAR2(20) NOT NULL,

USER_AGE INTEGER NOT NULL

);

CREATE SEQUENCE SEQ_USER_LOG

INCREMENT BY 1

START WITH 1

NOMAXVALUE

NOCYCLE

CACHE 10;

触发器4：

create or replace trigger tri_user_log

before insert or update or delete on tb_user for each row

begin

if inserting then

insert into tb_user_log values (SEQ_USER_LOG.NEXTVAL, 'INSERT', sysdate, :new.user_name, :new.user_age);

elsif updating then

insert into tb_user_log values (SEQ_USER_LOG.NEXTVAL, 'UPDATE', sysdate, :old.user_name, :old.user_age);

elsif deleting then

insert into tb_user_log values (SEQ_USER_LOG.NEXTVAL, 'DELETE', sysdate, :old.user_name, :old.user_age);

else

NULL;

end if;

end;

INSERTING：

DECLARE

V_AGE TB_USER.USER_AGE%TYPE;

V_NAME TB_USER.USER_NAME%TYPE;

BEGIN

FOR I IN 1..10 LOOP

SELECT DBMS_RANDOM.value(1, 100) INTO V_AGE FROM DUAL;

V_NAME := 'USER_' || V_AGE;

INSERT INTO TB_USER(USER_NAME, USER_AGE) VALUES (V_NAME, V_AGE);

END LOOP;

COMMIT;

END;

UPDATING：

DECLARE

CURSOR C_UPDATE IS SELECT USER_NAME, USER_AGE FROM TB_USER FOR UPDATE;

V_ROW C_UPDATE%ROWTYPE;

V_NAME TB_USER.USER_NAME%TYPE;

V_AGE TB_USER.USER_AGE%TYPE;

BEGIN

FOR V_ROW IN C_UPDATE LOOP

V_NAME := V_ROW.USER_NAME;

V_AGE := V_ROW.USER_AGE + 1;

UPDATE TB_USER SET USER_NAME=V_NAME, USER_AGE=V_AGE WHERE CURRENT OF C_UPDATE;

END LOOP;

COMMIT;

END;

DELETING：

delete from tb_user where id=1;

commit;

触发器5需要用到的表：

CREATE TABLE TB_USER_DEL_WHEN

(

USER_NAME VARCHAR2(20) NOT NULL,

USER_AGE INTEGER NOT NULL

);

触发器5：

create or replace trigger tri_user_del_when

before delete on tb_user

referencing old as old_value

for each row

when (old_value.user_age < 50)

begin

insert into tb_user_del_when values (:old_value.user_name, :old_value.user_age);

end;

测试5：

delete from tb_user where user_age<50;

commit;