Oracle 在插入、删除或更新时触发
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5171295/
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 Trigger on Insert or Delete or Update
提问by tylerjohnst
Trying to create an Oracle trigger that runs after a table is updated in any way. I've been googling this all morning and came up with this:
试图创建一个在表以任何方式更新后运行的 Oracle 触发器。我整个早上都在谷歌上搜索这个并想出了这个：
CREATE OR REPLACE TRIGGER gb_qty_change
AFTER UPDATE OR INSERT OR DELETE ON F_ITEM_STORE
FOR EACH ROW
DECLARE
v_qty V_AD_ON_HAND%rowtype;
v_isbn TD_ITEM_DESCRIPTION.TD_IDENTIFIER%type;
BEGIN
delete from gb_transaction where gb_tide = :new.ITST_ITEM_TIDE_CODE;
select TD_IDENTIFIER INTO v_isbn from TD_ITEM_DESCRIPTION where TD_TIDE = :new.ITST_ITEM_TIDE_CODE;
select * INTO v_qty from V_AD_ON_HAND where ITST_ITEM_TIDE_CODE = :new.ITST_ITEM_TIDE_CODE;
insert into gb_transaction(gb_tide, gb_isbn, gb_used_on_hand, gb_new_on_hand)
values(:new.ITST_ITEM_TIDE_CODE, v_isbn, v_qty.USED_ON_HAND, v_qty.NEW_ON_HAND);
END;
/
I'm trying to keep it to a single record per TIDE_CODE in the new table.
我试图将它保留为新表中每个 TIDE_CODE 的单个记录。
V_AD_ON_HAND is a view that pulls an inventory count. gb_transaction is my new table where I'm logging these events
V_AD_ON_HAND 是一个拉取库存计数的视图。gb_transaction 是我记录这些事件的新表
Comparing it to other peoples code it looks like it should run but I'm getting "Warning: Trigger created with compilation errors."
将它与其他人的代码进行比较，它看起来应该可以运行，但我收到“警告：触发器创建时有编译错误”。
回答by Michael Broughton
The problem, I believe is with the :new designations for a delete trigger. There is, after all, no NEW value as the record is expunged. You can only access the :OLD values on delete.
我认为问题在于删除触发器的 :new 名称。毕竟，随着记录被删除，没有新的价值。您只能在删除时访问 :OLD 值。
if you section the trigger by operation, you can do this.
如果按操作划分触发器，则可以执行此操作。
CREATE OR REPLACE TRIGGER gb_qty_change
AFTER UPDATE OR INSERT OR DELETE ON F_ITEM_STORE
FOR EACH ROW
DECLARE
v_qty V_AD_ON_HAND%rowtype;
v_isbn TD_ITEM_DESCRIPTION.TD_IDENTIFIER%type;
BEGIN
IF INSERTING or UPDATING then
... insert your existing code
ELSE
... do something similar with the :old values for the deleting case
end if;
END;
/
Incidentally, it is generally helpfull if you tell us WHAT the error is, not just that you had one. If compiling via SQL*Plus script, after the forward slash call to compile the trigger after the "end;" statement, add a line that says:
顺便说一句，如果您告诉我们错误是什么，而不只是告诉我们您有错误，通常会有所帮助。如果通过 SQL*Plus 脚本编译，则在“end;”之后的正斜杠调用编译触发器之后。声明，添加一行内容：
SHOW ERRORS TRIGGER YOUR_TRIGGER_NAME;
SHOW ERRORS TRIGGER YOUR_TRIGGER_NAME;
