插入 oracle 后触发触发
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9700808/
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
fire trigger after insert in oracle
提问by Bob Jarvis - Reinstate Monica
I'm very new for trigger, now this what i was trying. I've two tables INSERTEDand ORDER_INFO, both have the same column name ORDER_ID, ORDER_DATE. I've scenario, where client will be placing his/her order then, order information will be stored into INSERTEDtable, then by using this trigger, it'll insert into another table ORDER_INFOafter satisfying the condition, which has been written.
我对触发器很陌生，现在这就是我正在尝试的。我有两个表INSERTED和ORDER_INFO，它们都有相同的列名ORDER_ID，ORDER_DATE。我有一个场景，客户将下订单，然后将订单信息存储到INSERTED表中，然后通过使用此触发器，它会在满足已写入的条件后插入到另一个表ORDER_INFO 中。
create trigger tri_check
AFTER INSERT ON inserted FOR EACH ROW
DECLARE
v_date DATE;
BEGIN
SELECT order_date INTO v_date FROM inserted;
if (v_date)< (sysdate + 2) then
raiserror('You cannot take an order to be delivered less than 2 days from now',16, 1);
else
INSERT INTO orders_info
( order_id,order_date)
VALUES
(:new.order_id,v_date);
end if;
end;
But, when i'm executing the above trigger, then i'm getting this error.
但是，当我执行上述触发器时，我收到此错误。
ERROR at line 8: PL/SQL: SQL Statement ignored
6. SELECT order_date INTO v_date FROM inserted;
7. if (v_date)< (sysdate + 2) then
8. raiserror('You cannot take an order to be delivered less than 2 days from now',16, 1);
9. else
10. INSERT INTO orders_info
EDIT
编辑
Now, i made the same structure table into SYSTEMuser, and got the same error. Table or View does not exist
现在，我将相同的结构表设置为SYSTEM用户，并得到相同的错误。Table or View does not exist
Need help !! Thanks in advance !!
需要帮忙 ！！提前致谢 ！！
回答by Ollie
You can just use the :NEWand :OLDvalues instead of your select:
您可以只使用:NEW和:OLD值而不是您的选择：
CREATE TRIGGER tri_check
AFTER INSERT
ON inserted
FOR EACH ROW
DECLARE
BEGIN
IF :new.order_date < (SYSDATE + 2)
THEN
raiserror (
'You cannot take an order to be delivered less than 2 days from now',
16,
1);
ELSE
INSERT INTO orders_info (order_id, order_date)
VALUES (:new.order_id, :new.order_date);
END IF;
END;
What is your raiserrorprocedure? Do you have access permissions granted on it?
你的raiserror程序是什么？你有访问权限吗？
Hope it helps...
希望能帮助到你...
EDIT:
编辑：
OK, from your error, and the error you posted on @Bob Jarvis' answer, you might not have INSERTprivilege on the ORDERS_INFOtable. You also should check your permissions on the INSERTEDtable too.
好的，根据您的错误以及您在@Bob Jarvis 的回答中发布的错误，您可能没有INSERT该ORDERS_INFO表的特权。您还应该检查您对INSERTED表的权限。
Check your permissions with your DBA.
请与您的 DBA 检查您的权限。
If raiserroris not a defined procedure or you don't have access to it then use the RAISE_APPLICATION_ERRORmethod for raising an error as Bob suggests.
如果raiserror不是定义的过程或者您无权访问它，则使用RAISE_APPLICATION_ERROR方法来引发错误，如 Bob 建议的那样。
回答by Bob Jarvis - Reinstate Monica
The message seems to indicate a problem with the 'raiserror' procedure. I'm not familiar with such a procedure in standard PL/SQL - did you mean RAISE_APPLICATION_ERROR? However, and perhaps more to the point, when using a trigger there's no need to do a SELECT from the table. All the data being inserted is available to the trigger. I suggest changing your trigger to be something like the following:
该消息似乎表明“raiserror”过程存在问题。我不熟悉标准 PL/SQL 中的这样一个过程 - 你是说 RAISE_APPLICATION_ERROR 吗？然而，也许更重要的是，当使用触发器时，不需要从表中执行 SELECT。插入的所有数据都可用于触发器。我建议将您的触发器更改为如下所示：
create trigger tri_check
AFTER INSERT ON inserted
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
if :new.ORDER_DATE < sysdate + INTERVAL '2' DAY then
RAISE_APPLICATION_ERROR(-20000, 'You cannot take an order to be delivered less than 2 days from now');
else
INSERT INTO orders_info
(order_id, order_date)
VALUES
(:new.order_id, :new.ORDER_DATE);
end if;
end TRI_CHECK;
Share and enjoy.
分享和享受。
