ORACLE 和 TRIGGERS（插入、更新、删除）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2965521/
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
ORACLE and TRIGGERS (inserted, updated, deleted)
提问by LeftyX
I would like to use a trigger on a table which will be fired every time a row is inserted, updated, or deleted.
我想在表上使用触发器，每次插入、更新或删除行时都会触发该触发器。
I wrote something like this:
我写了这样的东西：
CREATE or REPLACE TRIGGER test001
AFTER INSERT OR DELETE OR UPDATE ON tabletest001
REFERENCING OLD AS old_buffer NEW AS new_buffer
FOR EACH ROW WHEN (new_buffer.field1 = 'HBP00')
and it works. Since I would like to do the same things if the row is inserted, updated, or deleted, I would like to know what's happening in the trigger. I think I can manage to find if the row is inserted or updated (I can check the old_buffer with the new_buffer). How can I know if the row has been deleted?
它有效。由于我想在插入、更新或删除行时做同样的事情，我想知道触发器中发生了什么。我想我可以设法找到该行是否已插入或更新（我可以使用 new_buffer 检查 old_buffer）。我如何知道该行是否已被删除？
回答by devio
From Using Triggers:
从使用触发器：
Detecting the DML Operation That Fired a Trigger
If more than one type of DML operation can fire a trigger (for example, ON INSERT OR DELETE OR UPDATE OF Emp_tab), the trigger body can use the conditional predicates INSERTING, DELETING, and UPDATING to check which type of statement fire the trigger.
检测触发触发器的 DML 操作
如果不止一种类型的 DML 操作可以触发触发器（例如，ON INSERT OR DELETE OR UPDATE OF Emp_tab），触发器主体可以使用条件谓词 INSERTING、DELETING 和 UPDATING 来检查触发触发器的语句类型。
So
所以
IF DELETING THEN ... END IF;
should work for your case.
应该适用于您的情况。
回答by LeftyX
I've changed my code like this and it works:
我已经像这样更改了我的代码并且它有效：
CREATE or REPLACE TRIGGER test001
AFTER INSERT OR UPDATE OR DELETE ON tabletest001
REFERENCING OLD AS old_buffer NEW AS new_buffer
FOR EACH ROW WHEN (new_buffer.field1 = 'HBP00' OR old_buffer.field1 = 'HBP00')
DECLARE
Operation NUMBER;
CustomerCode CHAR(10 BYTE);
BEGIN
IF DELETING THEN
Operation := 3;
CustomerCode := :old_buffer.field1;
END IF;
IF INSERTING THEN
Operation := 1;
CustomerCode := :new_buffer.field1;
END IF;
IF UPDATING THEN
Operation := 2;
CustomerCode := :new_buffer.field1;
END IF;
// DO SOMETHING ...
EXCEPTION
WHEN OTHERS THEN ErrorCode := SQLCODE;
END;
回答by Tony Andrews
The NEW values (or NEW_BUFFER as you have renamed them) are only available when INSERTING and UPDATING. For DELETING you would need to use OLD (OLD_BUFFER). So your trigger would become:
NEW 值（或重新命名的 NEW_BUFFER）仅在 INSERTING 和 UPDATING 时可用。对于 DELETING，您需要使用 OLD (OLD_BUFFER)。所以你的触发器会变成：
CREATE or REPLACE TRIGGER test001
AFTER INSERT OR DELETE OR UPDATE ON tabletest001
REFERENCING OLD AS old_buffer NEW AS new_buffer
FOR EACH ROW WHEN (new_buffer.field1 = 'HBP00' OR old_buffer.field1 = 'HBP00')
You may need to add logic inside the trigger to cater for code that updates field1 from 'HBP000' to something else.
您可能需要在触发器内添加逻辑以适应将 field1 从“HBP000”更新为其他内容的代码。
回答by rkellerm
Separate it into 2 triggers. One for the deletion and one for the insertion\ update.
将其分成 2 个触发器。一种用于删除，一种用于插入\更新。
