Oracle Trigger Typical Examples
发布时间:2020-12-12 15:38:22 所属栏目:百科 来源:网络整理
导读: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 i
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; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |