oracle 触发以防止从表中删除任何内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41964979/
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
Trigger to prevent Any Deleting from Table
提问by Azmina Hashim
a trigger for this new rental history table that prevents deletions from the table.
此新租赁历史记录表的触发器，可防止从表中删除。
CREATE OR REPLACE TRIGGER RENTALHIS_DEL
BEFORE DELETE ON RENTALHISTORY
BEGIN
dbms_output.put_line( 'Records can not be deleted');
END;
DELETE FROM RENTALHISTORY WHERE RENTALID = 1;
-- It is deleting before it says it can not delete
1 rows deleted.
Records can not be deleted
回答by Gurwinder Singh
dbms_output.put_line( 'Records can not be deleted');
dbms_output.put_line( 'Records can not be deleted');
The above just prints the text and trigger completes successfully and then delete happens anyway. What you wanna do instead is to raise an error to prevent the program from completing.
以上只是打印文本和触发器成功完成，然后删除仍然发生。您想要做的是引发错误以阻止程序完成。
Use standard procedure raise_application_errorto stop the program and raise error:
使用标准程序raise_application_error停止程序并引发错误：
CREATE OR REPLACE TRIGGER RENTALHIS_DEL
BEFORE DELETE ON RENTALHISTORY
BEGIN
raise_application_error(-20001,'Records can not be deleted');
END;
/
回答by Gordon Linoff
You want to raise an error, not print a message. The delete happens anyway:
您想引发错误，而不是打印消息。无论如何都会发生删除：
CREATE OR REPLACE TRIGGER RENTALHIS_DEL
BEFORE DELETE ON RENTALHISTORY
BEGIN
RAISE_APPLICATION_ERROR (-20000, 'Deletion not supported on this table');
END;
Alternatively, you could use an instead oftrigger to prevent the delete from taking place:
或者，您可以使用instead of触发器来防止发生删除：
CREATE OR REPLACE TRIGGER RENTALHIS_DEL
INSTEAD OF DELETE ON RENTALHISTORY
BEGIN
dbms_output.put_line( 'Records cannot be deleted');
END;
回答by Himanshu Ahuja
You can make use of commit after your delete statement and rollbackafter that to reach to the previous stage using Pragma.
您可以利用commit after your delete statement and rollback之后使用Pragma.
CREATE OR REPLACE TRIGGER
RENTALHIS_DEL
AFTER DELETE ON RENTALHISTORY
DECLARE
PRAGMA
AUTONOMOUS_TRANSACTION;
BEGIN
RAISE_APPLICATION_ERROR
(-20000, 'Deletion getting rolled
back');
ROLLBACK;
END;
