如何避免每个带有触发器的请求删除超过 1 行?

Question

我想编写一个触发器，拒绝删除每个请求超过 1 行

是否可以编写这样的触发器？如果是，你能帮忙提出一个想法或代码吗？

我正在使用oracle数据库11g并用sqldeveloper编写

订单表

id
detail
orderdate

Answer 1

我不确定为什么要这样做(多行删除比单行删除更有效(，但您可以使用复合触发器来实现此目的，例如：

create table mytable (id number);
create or replace trigger one_row_per_delete
for delete on mytable
compound trigger
row_count integer;
before statement is
begin
row_count := 0;
end before statement;
before each row is
begin
row_count := row_count + 1;
end before each row;
after statement is
begin
if row_count > 1 then
raise_application_error(-20000, 'Cannot delete more than one row per statement');
end if;
end after statement;
end one_row_per_delete;

测试：

insert into mytable values (1);
1 row inserted.
insert into mytable values (2);
1 row inserted.
insert into mytable values (3);
1 row inserted.
delete mytable where id = 1;
1 row deleted.
delete mytable;
ORA-20000: Cannot delete more than one row per statement
ORA-06512: at "SCOTT.ONE_ROW_PER_DELETE", line 18
ORA-04088: error during execution of trigger 'SCOTT.ONE_ROW_PER_DELETE'

Answer 2

例如，您可以使用触发器执行它

CREATE OR REPLACE TRIGGER denies_delete_trg
BEFORE DELETE
ON table_name
DECLARE
v_count_elements NUMBER := 0;
BEGIN
SELECT COUNT(1) INTO v_count_elements
FROM table_name
WHERE contitions_of_delete;
IF v_count_elements > 0 THEN
RAISE_APPLICATION_ERROR (-20000,'Too rows to delete.'); 
END IF;
END;