SQL引发应用程序错误触发器 [英] SQL Raise Application Error Trigger

问题描述

这将引发错误:ORA-04082:表级触发器中不允许使用新引用或旧引用"

This is throwing "Error: ORA-04082: NEW or OLD references not allowed in table level triggers"

我不确定我要去哪里.错误号不应该有所作为吗?

I'm not sure where I'm going wrong. The error number shouldn't make a difference should it?

    CREATE OR REPLACE TRIGGER REJECTION 
BEFORE INSERT OR UPDATE ON TEA_PREFS_T 
DECLARE temp NUMBER;
BEGIN
  SELECT COUNT(*) INTO temp FROM tea_prefs_t WHERE person = :new.drinkerid;
  IF (temp >=10) THEN
    raise_application_error(-20101, 'ERROR: CANNOT INSERT MORE THAN 10');
    ROLLBACK;
  END IF;
END;

推荐答案

错误提示，您只能在行级触发器中引用新的和旧的伪行，而不能在表级触发器中引用一次，无论该语句影响多少行.如果您用不同的饮酒者ID更新了两行，触发器将使用哪个值进行查询?

As the error suggests, you can only refer to the new and old pseudo-rows in a row-level trigger, not a table-level trigger, which fires once regardless of how many rows were affected by the statement. If you updated two rows with different drinker IDs, which value would the trigger use for its look-up?

要使其成为行级触发器，请添加 FOR EACH ROW :

To make it a row-level trigger, add FOR EACH ROW:

CREATE OR REPLACE TRIGGER REJECTION 
BEFORE INSERT OR UPDATE ON TEA_PREFS_T 
FOR EACH ROW
DECLARE
  temp NUMBER;
BEGIN
  SELECT COUNT(*) INTO temp FROM tea_prefs_t WHERE person = :new.drinkerid;
  IF (temp >=10) THEN
    raise_application_error(-20101, 'ERROR: CANNOT INSERT MORE THAN 10');
  END IF;
END;
/

您不能在触发器内进行提交或回滚；事务由插入/更新来决定是否执行此操作.

You can't commit or rollback from within a trigger; it's up to the transaction doing the insert/update to decide whether to do that.

但是，您也无法从要插入/更新的同一张表中进行选择；至少如果您一次尝试插入/更新多行，就会从中得到一个变异表错误.

However, you also can't select from the same table you're inserting into/updating; you'll get a mutating table error from this, at least if you attempt to insert/update multiple rows at once.

这篇关于SQL引发应用程序错误触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！