ORACLE 和 TRIGGERS(插入、更新、删除)

2022-01-01 00:00:00 oracle triggers

我想在表上使用触发器，每次插入、更新或删除行时都会触发该触发器.

I would like to use a trigger on a table which will be fired every time a row is inserted, updated, or deleted.

我是这样写的:

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')

它的工作原理.由于我想在插入、更新或删除行时做同样的事情，我想知道触发器中发生了什么.我想我可以设法找到该行是否已插入或更新(我可以使用 new_buffer 检查 old_buffer).我如何知道该行是否已被删除?

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?

推荐答案

来自 使用触发器:

检测触发的 DML 操作一个触发器

Detecting the DML Operation That Fired a Trigger

如果有不止一种类型的 DML 操作可以触发触发器(例如，ON插入或删除或更新emp_tab)，触发器主体可以使用条件谓词插入，删除和更新以检查哪个语句类型触发触发器.

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.

所以

IF DELETING THEN ... END IF;

应该适用于您的情况.

相关文章