Sync table A to Table B
CREATE TABLE A(ID INT, DESCRIPTION VARCHAR(10));
CREATE TABLE B(ID INT, DESCRIPTION VARCHAR(10));
CREATE OR REPLACE TRIGGER SYNC_A_TO_B
AFTER
INSERT OR
UPDATE OR
DELETE ON A
FOR EACH ROW --indicate that this is a row trigger, not table level trigger
BEGIN
DBMS_OUTPUT.PUT_LINE(:NEW.ID || '-' || :NEW.DESCRIPTION);
DBMS_OUTPUT.PUT_LINE(:OLD.ID || '-' || :OLD.DESCRIPTION);
CASE
WHEN INSERTING THEN
DBMS_OUTPUT.PUT_LINE('INSERTING');
INSERT INTO B VALUES (:NEW.ID, :NEW.DESCRIPTION);
WHEN UPDATING THEN
DBMS_OUTPUT.PUT_LINE('UPDATING');
UPDATE B SET B.DESCRIPTION = :NEW.DESCRIPTION WHERE B.ID = :NEW.ID;
WHEN DELETING THEN
DBMS_OUTPUT.PUT_LINE('DELETING');
DELETE FROM B WHERE B.ID = :OLD.ID;
END CASE;
END;
/
INSERT INTO A VALUES (1, 'A_TO_B');
SELECT * FROM A;
SELECT * FROM B;
UPDATE A SET A.DESCRIPTION = 'UPDATED' WHERE A.ID = 1;
SELECT * FROM A;
SELECT * FROM B;
DELETE FROM A WHERE A.ID = 1;
SELECT * FROM A;
SELECT * FROM B;
DROP TABLE A;
DROP TABLE B;
转载请注明原文地址: https://ju.6miu.com/read-963881.html
