"如何修复oracle pl / sql中的触发器?

Question

问题： 需要表 A 的触发器执行以下操作： 每次在表 A 中对列TRT_PROCEDURE值进行插入时，添加 1 到表B中的"TRT_INS_COUNT"列。如果TRT_PROCEDURE值 表 B 中不存在，在表 B 中添加一行以进行过程设置 TRT_INS_COUNT比 1。

每次对表 A 进行 DELETE 时，在 TRT_DEL_COUNT 中的列中添加 1 表 B 表示该过程值(如果它存在于表 B 中)。如果没有 存在于表 B 中，在表 B 中添加一行用于过程并设置 TRT_DEL_COUNT比 1。

每次对表 A 中的列TRT_PROCEDURE进行更新时，将 1 添加到 表B中的第TRT_UPD_COUNT栏。如果它不在表 B 中，请将一行添加到 表 B 为过程值，并将TRT_UPD_COUNT设置为 1。如果列 TRT_PROCEDURE表 A 中的值发生更改，则将 TRT_UPD_COUNT 1 添加到 旧过程值。

通过执行多个插入、删除和更新进行测试，然后显示表 B。

更新：每当将值 trt_procedure 插入表 B 时，第二个相同的trt_procedure(例如 '88-20')都无法正确计入其指定的计数字段。不知道哪里出了问题。

TABLE A
Name    Null     Type
TRT_ID  NOT NULL NUMBER(3)
PAT_NBR          NUMBER(4)
PHYS_ID          NUMBER(3)
TRT_PROCEDURE    VARCHAR2(5)
TRT_DATE         DATE

TABLE B
Name          Null     Type
TRT_PROCEDURE NOT NULL VARCHAR2(5)
TRT_INS_COUNT          NUMBER(3)
TRT_DEL_COUNT          NUMBER(3)
TRT_UPD_COUNT          NUMBER(3)
TEST SAMPLES
INSERT INTO A VALUES (11, 8031,101,'88-20',sysdate );
INSERT INTO A VALUES (12, 5872,101,'60-00',sysdate );
UPDATE A SET trt_procedure = '88-20' WHERE trt_id=6;
/*row trt_id =6 cloumn trt_procedure old value '54-60'
new value '88-20', which means Table B row '88-20' and
row '54-60 both trt_upd_count should add 1*/
DELETE FROM A WHERE trt_id=1; 
/*row trt_id =1 cloumn trt_procedure value also '88-20', which means
that Table B row '88-20' trt_del_count should also add 1*/
CREATE OR REPLACE TRIGGER trt_stats_trg
BEFORE INSERT OR UPDATE OR DELETE OF TRT_procedure ON A FOR EACH ROW
BEGIN
IF INSERTING THEN
UPDATE B SET trt_ins_count = trt_ins_count+1
WHERE B.trt_procedure = :new.trt_procedure;
IF SQL%NOTFOUND THEN
INSERT INTO B (trt_procedure, trt_ins_count)
VALUES (:new.trt_procedure, 1);
END IF;
ELSIF UPDATING THEN
UPDATE B SET trt_upd_count = trt_upd_count+1
WHERE B.trt_procedure = :old.trt_procedure;
IF SQL%NOTFOUND THEN
INSERT INTO B (trt_procedure, trt_upd_count)
VALUES (:old.trt_procedure, 1);
END IF;
UPDATE B SET trt_upd_count = trt_upd_count+1
WHERE B.trt_procedure = :new.trt_procedure;
IF SQL%NOTFOUND THEN
INSERT INTO B (trt_procedure, trt_upd_count)
VALUES (:new.trt_procedure, 1);
END IF;
ELSIF DELETING THEN
UPDATE B SET trt_del_count = trt_del_count+1
WHERE B.trt_procedure = :old.trt_procedure;
IF SQL%NOTFOUND THEN
INSERT INTO B (trt_procedure, trt_del_count)
VALUES (:old.trt_procedure, 1);
END IF;
END IF;
END trt_stats_trg;

Answer 1

来了，有一些修复。

表格：

create table a(
TRT_ID  NUMBER(3) NOT NULL ,
PAT_NBR          NUMBER(4),
PHYS_ID          NUMBER(3),
TRT_PROCEDURE    VARCHAR2(5),
TRT_DATE         DATE
);
create table b(
TRT_PROCEDURE VARCHAR2(5) NOT NULL,
TRT_INS_COUNT          NUMBER(3),
TRT_DEL_COUNT          NUMBER(3),
TRT_UPD_COUNT          NUMBER(3)
);

和触发器

CREATE OR REPLACE TRIGGER trt_stats_trg
BEFORE INSERT OR UPDATE OR DELETE ON A FOR EACH ROW
BEGIN
IF INSERTING THEN
UPDATE B SET trt_ins_count = nvl(trt_ins_count,0)+1
WHERE B.trt_procedure = :new.trt_procedure;
IF SQL%NOTFOUND THEN
INSERT INTO B (trt_procedure, trt_ins_count)
VALUES (:new.trt_procedure, 1);
END IF;
ELSIF UPDATING('TRT_PROCEDURE') THEN
if nvl(:old.trt_procedure, 'X') <> nvl(:new.trt_procedure, 'X') then
UPDATE B SET trt_upd_count = nvl(trt_upd_count,0)+1
WHERE B.trt_procedure = :old.trt_procedure;
IF SQL%NOTFOUND THEN
INSERT INTO B (trt_procedure, trt_upd_count)
VALUES (:old.trt_procedure, 1);
END IF;
UPDATE B SET trt_upd_count = nvl(trt_upd_count,0)+1
WHERE B.trt_procedure = :new.trt_procedure;
IF SQL%NOTFOUND THEN
INSERT INTO B (trt_procedure, trt_upd_count)
VALUES (:new.trt_procedure, 1);
END IF;
end if;
ELSIF DELETING THEN
UPDATE B SET trt_del_count = nvl(trt_del_count, 0)+1
WHERE B.trt_procedure = :old.trt_procedure;
IF SQL%NOTFOUND THEN
INSERT INTO B (trt_procedure, trt_del_count)
VALUES (:old.trt_procedure, 1);
END IF;
END IF;
END trt_stats_trg;
/

要点：

• 可以使用UPDATING('COLUMN_NAME')来检测 SQL 何时影响特定列。

• UPDATING时，您可能希望检查非更改更新，其中值为"ABC"的列正在更新为"ABC"。这通常发生在仅包含更新中的所有列的框架中，即使它们并没有真正更改。用类似于if nvl(:old.trt_procedure, 'X') <> nvl(:new.trt_procedure, 'X') then的东西来检测这一点。

表

• B没有计数的默认值，因此当原始触发器尝试更新表B时，它成功了...在 将NULL更新为NULL，因为当您将 1 添加到NULL时就会发生这种情况。可以通过为表中的列分配默认值0或使用NVL(trt_upd_count,0) + 1而不是trt_upd_count + 1来解决此问题。

• 最后，注意:new和:old的用法。看起来它们在此触发器中是正确的，但这取决于您的业务案例。

Answer 2

在触发器的IF INSERTING部分中，您有

UPDATE B
SET trt_ins_count = trt_ins_count+1
WHERE :old.trt_procedure = :new.trt_procedure;

插入新行时，:OLD伪行中的所有值均为 NULL，因此您的 UPDATE 永远不会更新任何内容。我想你的意思是

UPDATE B
SET trt_ins_count = trt_ins_count+1
WHERE B.trt_procedure = :new.trt_procedure;

试一试，看看这是否有助于解决您的问题。