创建触发器以在属性变为负数时删除行[oracle sql]? [英] Creating a trigger that deletes a row when an attribute becomes negative [oracle sql]?

问题描述

我想创建一个触发器，当其属性之一变为负数时将删除一行.到目前为止，我有这个，但它似乎不是有效的sql:

I would like to create a trigger that will delete a row when one of its attributes becomes negative. So far I have this, but it doesn't appear to be valid sql:

CREATE OR REPLACE TRIGGER ZERO_COPIES_TRIGGER
after
update of counter_attribute
on my_table
referencing new as new
for each row when(new.copies < 0)
begin
  delete from my_table where my_table.id = :new.id;
end;

推荐答案

这将无法正常工作.您不能在由行级触发器操作的表上执行DML.您将收到变异表"错误.

This is not going to work. You can't perform DML on a table which is being manipulated by a row-level trigger. You will get a "mutating table" error.

要获得所需的结果，最好的选择是设置一个标志或指示符列以标识该记录将被删除.然后，有一个单独的作业或过程或执行删除操作的任何内容.

To get the result you want, your best bet is to have a flag or indicator column to identify that the record is to be deleted. Then, have a separate job or process or whatever to actually perform the delete.

这篇关于创建触发器以在属性变为负数时删除行[oracle sql]?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！