建立一个Trigger , 使 A_TEST 中插入或更新或删除一笔数据, A_TEST_LOG 表中同步作业,保持两个Table中的数据一致 。
CREATE TABLE TONY.A_TEST
(
TCO_NO VARCHAR2(25 BYTE) NOT NULL,
LOC VARCHAR2(50 BYTE) NOT NULL,
QTY VARCHAR2(6 BYTE)
)
TABLESPACE TEST_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE TONY.A_TEST_LOG
(
TCO_NO VARCHAR2(25 BYTE) NOT NULL,
LOC VARCHAR2(50 BYTE) NOT NULL,
QTY VARCHAR2(6 BYTE)
)
TABLESPACE TEST_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE OR REPLACE TRIGGER TONY.Trig_test
AFTER INSERT OR UPDATE OR DELETE ON TONY.A_TEST
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
sqlstr varchar2(200);
BEGIN
IF INSERTING THEN
INSERT INTO TONY.A_TEST_LOG (Tco_No, Loc, Qty)
VALUES (:NEW.Tco_No, :NEW.Loc, :NEW.Qty);
ELSIF UPDATING THEN
UPDATE TONY.A_TEST_LOG
SET Tco_No=:NEW.Tco_No, Loc=:NEW.Loc, Qty=:NEW.Qty
WHERE Tco_No=:OLD.Tco_No ;
ELSIF DELETING THEN
DELETE FROM TONY.A_TEST_LOG WHERE Tco_No=:OLD.Tco_No ;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-605535/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/35489/viewspace-605535/