oracle 表字段触发器,oracle – PLSQL触发器更新另一个表中的字段值

在先前的答案中有一些未处理的角落案例.

如果插入行时报告表中已存在匹配的pk,该怎么办？ (我们通常不会期望会发生这种情况,但请考虑如果有人从orig_tab中删除了一行,然后再次插入它会发生什么.(这是在生产中出现的问题,而不是在测试中,在最不合时宜的时间.现在更好地为它做好计划.)

BEGIN

IF inserting THEN

-- insure we avoid duplicate key exception with a NOT EXISTS predicate

INSERT INTO rep_tab(pk,name)

SELECT :new.pk, :new.name FROM DUAL

WHERE NOT EXISTS (SELECT 1 FROM rep_tab WHERE pk = :new.pk);

-- if row already existed, there's a possibility that name does not match

UPDATE rep_tab t SET t.name = :new.name

WHERE t.pk = :new.pk;

-- could improve efficiency of update by checking if update is actually

-- needed using a nullsafe comparison ( t.name <=> :new.name );

ELSIF updating THEN

-- handle updates to pk value (note: the row to be updated may not exist

-- so we need to fallthru to the merge)

IF :new.pk <> :old.pk THEN

UPDATE rep_tab t

SET t.pk = :new.pk

, t.name = :new.name

WHERE t.pk = :old.pk ;

END IF;

MERGE INTO rep_tab d

USING DUAL ON (d.pk = :old.pk)

WHEN MATCHED THEN

UPDATE SET d.name = :new.name

WHEN NOT MATCHED THEN

INSERT (d.pk,d.name) VALUES (:new.pk,:new.name);

END IF;

END;