触发器

create or replace trigger tri_comms
after insert on tbl_users_comment
for each row
declare
  v_id number;
  v_cls varchar2(1);
begin
  v_id := :new.data_id;
  v_cls := :new.comm_cls;
  if v_cls = '1' then
    update tbl_tech_achv set comms = comms + 1 where id = v_id;
  end if;
  if v_cls = '2' then
    update tbl_tech_demand set comms = comms + 1 where id = v_id;
  end if;
  if v_cls = '3' then
    update tbl_info_expert set comms = comms + 1 where id = v_id;
  end if;
end;

CREATE OR REPLACE TRIGGER "FYSCIENCE"."TRI_COMMS"
after insert on tbl_users_comment
for each row
declare
  v_id number;
  v_cls varchar2(1);
begin
  v_id := :new.data_id;
  v_cls := :new.comm_cls;
  if v_cls = '1' then
    update tbl_tech_achv set comms = comms + 1 where id = v_id;
  end if;
  if v_cls = '2' then
    update tbl_tech_demand set comms = comms + 1 where id = v_id;
  end if;
  if v_cls = '3' then
    update tbl_info_expert set comms = comms + 1 where id = v_id;
  end if;
end;

CREATE TRIGGER TRI_COLLS
after insert
on tbl_base_colls
for each row
declare
    v_id number;
    v_cls varchar2(1);
begin
    v_id := :new.data_id;
    v_cls := :new.colls_cls;
    if v_cls = '1' then
            update tbl_tech_achv set colls = colls + 1 where id = v_id;
    end if;
    if v_cls = '2' then
        update tbl_tech_demand set colls = colls + 1 where id = v_id;
        end if;
        if v_cls = '3' then
            update tbl_info_expert set colls = colls + 1 where id = v_id;
        end if;
        if v_cls = '4' then
            update tbl_users_member set colls = colls + 1 where id = v_id;
        end if;
    if inserting then
        
    end if;
    if deleting then
        if v_cls = '1' then
            update tbl_tech_achv set colls = colls - 1 where id = v_id;
        end if;
        if v_cls = '2' then
            update tbl_tech_demand set colls = colls - 1 where id = v_id;
        end if;
        if v_cls = '3' then
            update tbl_info_expert set colls = colls - 1 where id = v_id;
        end if;
        if v_cls = '4' then
            update tbl_users_member set colls = colls - 1 where id = v_id;
        end if;
    end if;
end;

最终版=============================================================================================================

create or replace trigger "TRI_COLLS"
before insert or delete on tbl_base_colls
for each row
declare
    v_id number;
    v_cls varchar2(1);
begin
    if inserting then
      v_id := :new.data_id;
      v_cls := :new.colls_cls;
      if v_cls = '1' then
          update tbl_tech_achv set colls = colls + 1 where id = v_id;
      end if;
      if v_cls = '2' then
          update tbl_tech_demand set colls = colls + 1 where id = v_id;
      end if;
      if v_cls = '3' then
          update tbl_info_expert set colls = colls + 1 where id = v_id;
      end if;
      if v_cls = '4' then
          update tbl_users_member set colls = colls + 1 where id = v_id;
      end if;
    end if;
    if deleting then
      v_id := :old.data_id;
      v_cls := :old.colls_cls;
      if v_cls = '1' then
          update tbl_tech_achv set colls = colls - 1 where id = v_id;
      end if;
      if v_cls = '2' then
          update tbl_tech_demand set colls = colls - 1 where id = v_id;
      end if;
      if v_cls = '3' then
          update tbl_info_expert set colls = colls - 1 where id = v_id;
      end if;
      if v_cls = '4' then
          update tbl_users_member set colls = colls - 1 where id = v_id;
      end if;
    end if;
end;

 

create or replace trigger "TRI_COMMS"
after update on tbl_users_comment
for each row
declare
  v_id number;
  v_cls varchar2(1);
  v_state varchar2(1);
  v_isdel varchar2(1);
begin
  v_id := :old.data_id;
  v_cls := :old.comm_cls;
  v_state := :new.comm_state;
  v_isdel := :new.is_del;
  if v_state = '0' then
    if v_cls = '1' then
      update tbl_tech_achv set comms = comms + 1 where id = v_id;
    end if;
    if v_cls = '2' then
      update tbl_tech_demand set comms = comms + 1 where id = v_id;
    end if;
    if v_cls = '3' then
      update tbl_info_expert set comms = comms + 1 where id = v_id;
    end if;
  end if;
  if v_isdel = '1' then
    if v_cls = '1' then
      update tbl_tech_achv set comms = comms - 1 where id = v_id;
    end if;
    if v_cls = '2' then
      update tbl_tech_demand set comms = comms - 1 where id = v_id;
    end if;
    if v_cls = '3' then
      update tbl_info_expert set comms = comms - 1 where id = v_id;
    end if;
  end if;
end;