“如何在oracle /sql中修复触发器？

Question

问题:表A需要一个触发器，用于执行以下操作:每次在表A中对列TRT_PROCEDURE值进行插入时，将1添加到表B中的“TRT_INS_COUNT”列。如果表B中不存在TRT_PROCEDURE值，则将一行添加到表B中，以便将TRT_INS_COUNT设置为1。

每次对表A进行删除时，如果表B中存在该过程值，则将1添加到表B中的表B中的列TRT_DEL_COUNT中，如果表B中不存在该列，则为该过程向表B中添加一行，并将TRT_DEL_COUNT设置为1。

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

通过执行多个插入、删除和更新来进行测试，然后显示表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伪值中的所有值都为空，因此您的更新将永远不会更新任何内容。我想你是说

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

试试看这是否有助于解决你的问题。