在所有行中触发条件 [英] Trigger condition in all rows

问题描述

我正在尝试实施交通管制系统.在观察"表中引入雷达观察时，触发器应检查同一辆车上是否有观察行，然后计算罚款费用.

I'm trying to implement a traffic fine control system. When a radar observation is introduced in the table OBSERVACION the trigger should check if there is a row with an observation over the same car, and then calculate the fine cost.

我希望触发器检查表的每一行中的条件语句.我的意思是，我希望触发器条件能够检查表中的所有值，并确定其中之一是否等于引入的新值.像这样:

I want a trigger to check a condition statement in every row of the table. I mean, I want the trigger condtion to check all the values in the table and find if one of them is equal to the NEW value introduced. Something like this:

WHEN(NEW.fecha_foto = fecha_foto)AND...

fecha_foto在表中的任何值.

Where fecha_foto is any value in the table.

有可能吗?

谢谢.

推荐答案

对拥有触发器的同一表发出DML(包括SELECT)的触发器会引发ORA-4091(变异表异常)的问题. Oracle之所以提出这个错误，是因为Oracle希望交易的结果是确定性的，并且在自己的表上发布DML的触发器将不确定性引入了诉讼程序中.

A trigger which issues DML (including SELECT) against the same table which owns the trigger raises the spectre of ORA-4091, the mutating table exception. Oracle hurls this error because it expects the outcome of a transaction to be deterministic, and a trigger which issue DML on its own table introduces uncertainty into proceedings.

可变表是不良设计(尤其是不良数据模型)的可靠指标，在这里似乎就是这种情况.

Mutating table is a reliable indicator of bad design, specifically a bad data model and that seems to be the case here.

如果罚款的价值取决于一次观察的结果，那么可以将FINE用作OBSERVACION表的一列.但是，您的业务规则表明，如果价值在一定时期内，则价值可能取决于多项观察结果.在这种情况下，罚款应在其自己的表中.您将需要在OBSERVACION和MULTA之间使用外键，以指示哪些观察值受到特别罚款.

If the value of a fine is dependent on the outcome of a single observation then it is okay for FINE to be a column on the OBSERVACION table. However your business rules indicate that the value may depend on several observations, if they fall within a certain period. In this scenario the fine should be in a table of its own. You would need a foreign key between OBSERVACION and MULTA to indicate which observations are covered by a particular fine.

这篇关于在所有行中触发条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！