DML类型触发器
CREATE TABLE LOG_TAB
(
ID VARCHAR2(10) NOT NULL,
OPER_TABLE VARCHAR2(20),
OPER_KD VARCHAR2(10),
OPER_TABLE_PRK VARCHAR2(50),
OPER_DATE DATE,
constraint LOG_TAB_PRK primary key (ID)
)
--step2创建用做LOG_TAB表主键的自增长序列
CREATE SEQUENCE LOG_TAB_ID
MINVALUE 1000000000
MAXVALUE 9999999999
START WITH 1000000000
INCREMENT BY 1
--step3 创建触发器
CREATE TRIGGER PRODUCTINFO_OPER_TGR
BEFORE INSERT
ON PRODUCTINFO
FOR EACH ROW --每增加一行就会触发一次
BEGIN
IF INSERTING THEN
INSERT INTO LOG_TAB
VALUES
(LOG_TAB_ID.NEXTVAL,
'PRODUCTINFO',
'INSERT',
:NEW.PRODUCTID,
SYSDATE);
DBMS_OUTPUT.PUT_LINE('插入数据主键是 ' || :new.PRODUCTID);
END IF;
END
--行级触发器使用:new或:old来访问变更前和变更后的数据
CREATE TRIGGER PRODUCTINFO_OPER_DML_TGR
AFTER INSERT OR UPDATE OR DELETE
ON PRODUCTINFO
FOR EACH ROW
BEGIN
CASE
WHEN INSERTING THEN
INSERT INTO LOG_TAB
VALUES
(LOG_TAB_ID.NEXTVAL,
'PRODUCTINFO',
'INSERT',
:NEW.PRODUCTID,
SYSDATE);
DBMS_OUTPUT.PUT_LINE('插入数据主键是 ' || :new.PRODUCTID);
WHEN UPDATEING THEN
INSERT INTO LOG_TAB
VALUES
(LOG_TAB_ID.NEXTVAL,
'PRODUCTINFO',
'INSERT',
:NEW.PRODUCTID,
SYSDATE);
DBMS_OUTPUT.PUT_LINE('修改数据主键是 ' || :OLD.PRODUCTID);
WHEN DELETEING THEN
INSERT INTO LOG_TAB
VALUES
(LOG_TAB_ID.NEXTVAL,
'PRODUCTINFO',
'INSERT',
:NEW.PRODUCTID,
SYSDATE);
DBMS_OUTPUT.PUT_LINE('修改数据主键是 ' || :OLD.PRODUCTID);
END CASE;
END;
CREATE TRIGGER PRODUCTINFO_OPER_CHK_TGR
BEFORE UPDATE OF PRODUCTPRICE ON PRODUCTINFO
FOR EACH ROW
BEGIN
IF (TO_CHAR(SYSDATE,'dd') = 25 AND :OLD.PRODUCTPRICE >3000) THEN RAISE_APPLICATION_ERROR(-20000,'今天是25号,不允许修改价格高于3000的数据!');
INSERT INTO LOG_TAB
VALUES
(LOG_TAB_ID.NEXTVAL,
'PRODUCTINFO',
'INSERT',
:NEW.PRODUCTID,
SYSDATE);
DBMS_OUTPUT.PUT_LINE('修改数据主键是 ' || :NEW.PRODUCTID);
END;
CREATE TRIGGER PRODUCTINFO_WHEN_OPER_CHK_TGR
BEFORE INSERT ON PRODUCTINFO
FOR EACH ROW
WHEN (NEW.CATEGORY = '0100050001')
BEGIN
DBMS_OUTPUT.PUT_LINE('原价格: ' || :NEW.PRODUCTPRICE);
:NEW.PRODUCTPRICE := :NEW.PRODUCTPRICE * 0.9;
DBMS_OUTPUT.PUT_LINE('打折后价格: ' || :NEW.PRODUCTPRICE);
END;
CREATE TABLE CATEGROYINFO_BAK AS SELECT * FROM CATEGROYINFO WHERE 1<>1
--该语句表示复制表结构但不复表数据
--step2 创建触发器
CREATE TRIGGER MUTLI_OPER_CHK_TGR
AFTER UPDATE OF CATEGROYID ON CATEGROYINFO
FOR EACH ROW
BEGIN
UPDATE PRODUCTINFO
SET CATEGORY = :NEW.CATEGROYID
WHERE CATEGORY = :OLD.CATEGROYID;
INSERT INTO CATEGROYINFO_BAK VALUES (:OLD.CATEGROYID, :OLD.CATEGROYNAME);
DBMS_OUTPUT.PUT_LINE('数据已存入 CATEGROYINFO_BAK表中');
END;