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;