更新不同表格的Oracle PL/SQL触发器
我正在为大学做这个练习,而且我一直坚持了一个星期。我需要创建一个触发器,以便当表“tbvale”上的状态列发生更改时,相应地在列“tbfuncionario”上发生一些更新。 我意识到我的代码可能看起来很笨重,甚至可能不合适,但它是一个旨在教授特定事物的单独练习。 这就是我想出迄今:更新不同表格的Oracle PL/SQL触发器
CREATE OR REPLACE TRIGGER status_chg
AFTER UPDATE OF status ON tbvale
FOR EACH ROW
DECLARE
nvd tbfuncionario.numvalesdescontados%type;
nva tbfuncionario.numvalesaberto%type;
vtva tbfuncionario.valortotalvalesaberto%type;
nve tbfuncionario.numvalesemitidos%type;
vv tbvale.valorvale%type;
cod tbvale.fkcodmat%type;
pragma autonomous_transaction;
BEGIN
IF (:NEW.status <> :OLD.status) THEN
SELECT valorvale INTO vv FROM tbvale;
SELECT fkcodmat INTO cod FROM tbvale;
IF (:NEW.status = 2) THEN
nvd := 1;
nva := -1;
nve := 0;
vv := vv - 1;
ELSE
nvd := 0;
nve := 1;
nva := 1;
vv := vv + 1;
END IF;
UPDATE tbfuncionario
SET numvalesdescontados = numvalesdescontados + nvd,
numvalesaberto = numvalesaberto + nva,
numvalesemitidos = numvalesemitidos + nve,
valortotalvalesaberto = valortotalvalesaberto + vv
WHERE pkcodmat = cod;
END IF;
END;
pkcodmat是tbfuncionario的PK和fkcodmat是tbvale引用pkcodmat的FK。
目前,当我运行
UPDATE tbvale SET STATUS = 2 WHERE PKCODVALE = 3;
我得到以下错误:
ERROR: ORA-01422: exact fetch returns more than requested
number of rows ORA-06512: at "ULBRA.STATUS_CHG", line 11
ORA-04088: error during execution of trigger 'ULBRA.STATUS_CHG'
Error Code: 1422
Query = UPDATE tbvale SET STATUS = 2 WHERE PKCODVALE = 3
我不明白为什么它会“返回多于行的请求数量”因为只有一个行,其中pkcodmat等于fkcodmat。
我很欣赏任何有关为什么会发生这种情况以及我如何解决它的见解。
你不需要这些查询这是导致异常:
SELECT valorvale INTO vv FROM tbvale;
SELECT fkcodmat INTO cod FROM tbvale;
假设你想从tbvale
行的值更新,使用:old
或:new
记录有关数据如
vv := :new.valorvalue;
cod := :new.fkcodmat;
谢谢!这似乎工作。我会做一些测试,但至今如此好! – pharaohlxvi
该触发器看起来相当不错。以下是一些要点:
-
IF (:NEW.status <> :OLD.status)
未检测到更改表单并为NULL,以防列可为空。身体内部不是IF
,而是使用触发器WHEN
来代替。 - 您正在使用变量的列类型。然而,这些只能保持整数。因此不需要列类型。这是行不通的。你需要一行的价值,所以你需要一个
WHERE
条款。但是由于您对当前记录值感兴趣,因此您可以简单地使用:new.valorvale
。 - 与
SELECT fkcodmat INTO cod FROM tbvale
相同。 - 最后,自主触发器需要
COMMIT
或ROLLBACK
。
这里是修正触发:
CREATE OR REPLACE TRIGGER status_chg
AFTER UPDATE OF status ON tbvale
FOR EACH ROW
WHEN (DECODE(NEW.STATUS, OLD.STATUS, 0, 1) = 1)
DECLARE
v_vd integer;
v_va integer;
v_ve integer;
v_vv tbvale.valorvale%type;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF (:NEW.status = 2) THEN
v_vd := 1;
v_va := -1;
v_ve := 0;
v_vv := :NEW.valorvale - 1;
ELSE
v_vd := 0;
v_va := 1;
v_ve := 1;
v_vv := :NEW.valorvale + 1;
END IF;
UPDATE tbfuncionario
SET numvalesdescontados = numvalesdescontados + v_vd,
numvalesaberto = numvalesaberto + v_va,
numvalesemitidos = numvalesemitidos + v_ve,
valortotalvalesaberto = valortotalvalesaberto + v_vv
WHERE pkcodmat = :NEW.fkcodmat;
COMMIT;
END;
这里是不变量相同的触发:
CREATE OR REPLACE TRIGGER status_chg
AFTER UPDATE OF status ON tbvale
FOR EACH ROW
WHEN (DECODE(NEW.STATUS, OLD.STATUS, 0, 1) = 1)
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE tbfuncionario
SET numvalesdescontados = numvalesdescontados + CASE WHEN :NEW.status = 2 THEN 1 ELSE 0 END
, numvalesaberto = numvalesaberto + CASE WHEN :NEW.status = 2 THEN -1 ELSE 1 END
, numvalesemitidos = numvalesemitidos + CASE WHEN :NEW.status = 2 THEN 0 ELSE 1 END
, valortotalvalesaberto = valortotalvalesaberto + :NEW.valorvale + CASE WHEN :NEW.status = 2 THEN -1 ELSE 1 END
WHERE pkcodmat = :NEW.fkcodmat;
COMMIT;
END;
嗨,谢谢你的回答。我了解你所做的改变。尽管我在复制代码时犯了一个错误。价值:NEW.valorvale应减去或添加到valortotalvalesaberto,而不是增加或减少1(不知道我是否让自己清楚)。这很容易用(*)而不是(+)来纠正,是吗? (valortotalvalesaberto = valortotalvalesaberto +:NEW.valorvale * CASE WHEN:NEW.status = 2 THEN -1 ELSE 1 END)。除此之外,当我尝试你的代码时,我得到了“错误:ORA-00906:缺少左括号”。 – pharaohlxvi
是的,你的解决方案可以正常工作。另一种方法是'= valortotalvalesaberto + CASE WHEN:NEW.status = 2 THEN - :NEW.valorvale ELSE:NEW.valorvale END'。抱歉缺少括号; WHEN条款需要它们。我已经更新了我的答案。 –
这工作!非常感谢! – pharaohlxvi
where子句添加到触发体内的select语句。 – DwB
这些查询必须返回单个行:'SELECT valorvale INTO vv FROM tbvale;' 'SELECT fkcodmat INTO cod FROM tbvale;' – L30n1d45
“'Ive been stuck for a week”如果您运行了'SELECT valorvale FROM tbvale;'你自己可以救了你六天,二十三小时五十九天的窒息。 ' – APC