即時將 USERTEST 表的增、刪、改同步至結構相同的 USERTEST_TEMP 備份表。
-- 主表
CREATE TABLE USERTEST (
NAME VARCHAR2(20) NOT NULL,
AGE NUMBER,
ISDELETE VARCHAR2(4)
);
-- 備份表
CREATE TABLE USERTEST_TEMP (
NAME VARCHAR2(20) NOT NULL,
AGE NUMBER,
ISDELETE VARCHAR2(4)
);
CREATE OR REPLACE TRIGGER tr_user_temp
BEFORE INSERT OR UPDATE OR DELETE ON usertest
FOR EACH ROW
DECLARE
BEGIN
IF INSERTING THEN
INSERT INTO usertest_temp (name, age, isdelete)
VALUES (:new.name, :new.age, :new.isdelete);
ELSIF DELETING THEN
DELETE FROM usertest_temp WHERE name = :old.name;
ELSIF UPDATING THEN
UPDATE usertest_temp
SET age = :new.age, isdelete = :new.isdelete
WHERE name = :old.name;
END IF;
END;
/
對 USERTEST 執行 DML 操作,USERTEST_TEMP 將自動同步變更。
BEFORE (事件前) / AFTER (事件後)FOR EACH ROW 列級觸發 (每列觸發一次);省略則為陳述式觸發 (整個 SQL 觸發一次):NEW 為操作後值,:OLD 為操作前值;PL/SQL 中需加冒號,WHEN 子句則不加。