从同一表触发选择 [英] Trigger selection from same table

问题描述

我想问一个关于触发器的问题。假设我有一个表T，并且在更新之前需要一个触发器。但是，我有一个问题。我需要使用触发器中T的其他行来检查条件。我的问题是：哪个RDBMS支持此功能？在哪里可以触发触发器的触发器在同一张表上编写执行选择的触发器。例如：

I would like to ask a question about triggers. Let's say that I have a table T and I need a trigger before update. However, I have a problem. I need to check a condition using the other rows of T from the trigger. My question is: Which RDBMS's support this? Where can I write triggers which perform selection(s) on the same table where the trigger fires. For instance:

CREATE TRIGGER updtrigger BEFORE UPDATE ON Employee
    -> FOR EACH ROW
    -> BEGIN
    -> IF NEW.Salary<=500 THEN
    -> SET NEW.Salary=10000;
    -> ELSEIF NEW.Salary>500 and NEW.Salary < 600 THEN
    -> SET NEW.Salary=15000;
    -> ELSEIF NEW.Salary > (select MAX(Salary) from Employee)
    -> Set NEW.Salary = 601;
    -> END IF;
    -> END
    -> //

谢谢，

最好

Lajos Arpad。

Lajos Arpad.

推荐答案

给定的触发器将抛出例如，在Oracle中有一个Mutating table异常，但是在Oracle中有一个解决方案，例如，允许此触发器并且它可以正常工作：

The given trigger will throw an Mutating table exception in Oracle, for example, but there is a solution in Oracle, for instance this trigger is allowed and it works fine:

CREATE or replace TRIGGER updtrigger BEFORE UPDATE ON Employees
     FOR EACH ROW
DECLARE
 pragma autonomous_transaction;
 n number;
     BEGIN
     select MAX(Salary) into n from Employees;
     IF :NEW.Salary<=500 THEN
          DBMS_OUTPUT.PUT_LINE('kisebb mint 500');
          :NEW.salary:=n;
    end if;
commit;
     END;

这篇关于从同一表触发选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！