Oracle Trigger Typical Examples

最新推荐文章于 2024-02-27 10:57:15 发布

落落叶叶无声

最新推荐文章于 2024-02-27 10:57:15 发布

阅读量438

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle 文章标签： oracle trigger sync

本文链接：https://blog.youkuaiyun.com/u012557814/article/details/53434632

Oracle 专栏收录该内容

20 篇文章

订阅专栏

本文介绍了一个使用触发器实现的表A到表B的数据同步过程。通过创建一个触发器，当表A发生插入、更新或删除操作时，相应的操作会同步到表B中。示例包括了创建表、触发器的定义以及执行SQL语句来演示同步的效果。

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;