oracle PLSQL触发器更新另一个表中的字段值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11135507/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PLSQL Trigger to update field value in another table
提问by Jaanna
I am quite new to triggers so obviously I am doing something wrong somewhere. I am working on a report table which will get the data from original tables. For the sake of simplicity, let's say that there is one table and then there is one reporting table.
我对触发器很陌生，所以很明显我在某个地方做错了。我正在处理一个报告表，它将从原始表中获取数据。为简单起见，假设有一张表，然后有一张报表。
Original table (orig_tab)
原始表 (orig_tab)
CREATE TABLE orig_tab (
PK NUMBER(8) not null,
NAME VARCHAR2(20) ,
);
INSERT INTO orig_tab (PK, NAME) VALUES (1, 'AAA');
INSERT INTO orig_tab (PK, NAME) VALUES (2, 'BBB');
INSERT INTO orig_tab (PK, NAME) VALUES (3, 'CCC');
Then there is reporting table (rep_tab)
然后是报表（rep_tab）
CREATE TABLE rep_tab (
PK NUMBER(8) not null,
NAME VARCHAR2(20) ,
);
Now from user inteface, someone changes the value of record 2. Obviously, this should be treated as an insert (because this record doesn't exist) for reporting table. Then after sometime, the value is changed so it is an update case for reporting table.
现在从用户界面，有人更改了记录 2 的值。显然，这应该被视为报告表的插入（因为该记录不存在）。然后过了一段时间，该值发生了变化，因此它是报表的更新案例。
Question: How may I make this kind of trigger? I assume that it is a merge statemement case.
问题：我怎样才能制作这种触发器？我假设这是一个合并语句案例。
This is what I have done:
这就是我所做的：
create or replace trigger vr_reporting_trigger
after update on orig_tab
for each row
begin
MERGE INTO rep_tab d
USING (SELECT pk FROM orig_tab) s
ON (d.pk = s.pk)
WHEN MATCHED THEN
UPDATE SET d.pk = s.pk,
d.name = s.name
WHEN NOT MATCHED THEN
INSERT (d.pk, d.name) VALUES (s.pk, s.name);
end vr_reporting_trigger;
Any suggestions or recommendations that can help me to figure it out? Thanks.
任何可以帮助我弄清楚的建议或建议？谢谢。
采纳答案by spencer7593
There are some corner cases that aren't handled in previous answers.
以前的答案中没有处理一些极端情况。
What if a matching pk already exists in the reporting table, when a row is inserted. (We wouldn't normally expect this to happen, but consider what would happen if someone deleted a row from the orig_tab, and then inserted it again. (This is the kind of problem that's going to crop up in production, not in test, at the most inopportune time. Better to plan for it now.)
如果在报告表中已经存在匹配的 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;
回答by Sathyajith Bhat
Merge statement sounds like a plan, except that the trigger won't fire when you're doing the first insert because you've mentioned it's an AFTER UPDATE trigger, not an AFTER INSERT trigger.
Merge 语句听起来像是一个计划，只是在您进行第一次插入时不会触发触发器，因为您已经提到它是 AFTER UPDATE 触发器，而不是 AFTER INSERT 触发器。
Also, the SELECT pk FROM orig_tabwill result in Mutating table problem.
此外，这SELECT pk FROM orig_tab将导致变异表问题。
Better way would be to define an AFTER INSERT OR UPDATE trigger, combine it with INSERT/UPDATING keywords to handle inserts/updates & use :new/:oldto handle new data & old data respectively.
更好的方法是定义一个 AFTER INSERT OR UPDATE 触发器，将它与 INSERT/UPDATING 关键字结合来处理插入/更新和使用:new/:old分别处理新数据和旧数据。
CREATE OR replace TRIGGER vr_reporting_trigger
AFTER INSERT OR UPDATE ON orig_tab
FOR EACH ROW
BEGIN
IF inserting THEN
INSERT INTO rep_tab
(pk,
name)
VALUES (:NEW.pk,
:NEW.name);
ELSIF updating THEN
UPDATE rep_tab r
SET name = :NEW.name
WHERE r.pk = :old.pk;
END IF;
END vr_reporting_trigger;
回答by Gaurav Soni
This is an Extension of Sathya Answeras Jaannaasked about if the record is updating in orrig_tab and no corresponding record in rep_tab then the below logic will cater the request below .Please don't judge me with this answer as this solution belongs to Sathya
这是一个扩展Sathya Answer为Jaanna被问及如果记录在orrig_tab更新并没有相应的rep_tab记录，则下面的逻辑将满足下面，请您及时的要求，因为这解决方案属于沙迪亚不同意这个答案评判我
CREATE OR replace TRIGGER vr_reporting_trigger
AFTER INSERT OR UPDATE ON orig_tab
FOR EACH ROW
BEGIN
IF inserting THEN
INSERT INTO rep_tab
(pk,
name)
VALUES (:NEW.pk,
:NEW.name);
ELSIF updating THEN
MERGE INTO rep_tab d
USING DUAL
ON (d.pk =:OLD.pk)
WHEN MATCHED THEN
UPDATE SET d.name = :OLD.name
WHEN NOT MATCHED THEN
INSERT (d.pk,d.name) VALUES (:OLD.PK,:NEW.PK );
END IF;
END vr_reporting_trigger;
