新手上路求帮助，触发器

[heng_M]

create or replace TRIGGER tr_t_son_i AFTER
    INSERT ON t_son
    FOR EACH ROW
DECLARE
    sid INT; mid VARCHAR(99); ins INT; sub INT;  
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 --   SELECT :new.id,:new.main_id,:new.ins,:new.sub INTO sid,mid,ins,sub FROM t_son WHERE ROWNUM = 1;
    SELECT id,main_id,ins,sub INTO sid,mid,ins,sub FROM t_son where ROWNUM = 1;
 --   SELECT :new.id,:new.main_id,:new.ins,:new.sub INTO 15,1,1024,null FROM t_son WHERE ROWNUM = 1;

    IF( ins is null ) THEN
        dbms_output.put_line('SID');
        UPDATE t_son SET t_son.bal = ( SELECT ( t.bal - t_son.sub ) FROM t_main t WHERE id = mid ) WHERE t_son.id = sid;
        COMMIT;
    ELSE
        UPDATE t_son SET t_son.bal = ( SELECT ( t.bal + t_son.ins ) FROM t_main t WHERE id = mid ) WHERE t_son.id = sid;
        COMMIT;
   
    END IF;
        commit;
END;
    

触发器未执行，不知道问题出在哪了

--------------------------------------------------------
--  DDL for Table T_MAIN
--------------------------------------------------------

  CREATE TABLE "SYSTEM"."T_MAIN" 
   (	"ID" NUMBER(*,0), 
	"BAL" NUMBER(*,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Table T_SON
--------------------------------------------------------

  CREATE TABLE "SYSTEM"."T_SON" 
   (	"ID" NUMBER(*,0), 
	"INS" NUMBER(*,0), 
	"SUB" NUMBER(*,0), 
	"BAL" NUMBER(*,0), 
	"MAIN_ID" NUMBER(*,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Index T_MAIN_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "SYSTEM"."T_MAIN_PK" ON "SYSTEM"."T_MAIN" ("ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Index T_SON_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "SYSTEM"."T_SON_PK" ON "SYSTEM"."T_SON" ("ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Trigger TR_T_SON_I
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "SYSTEM"."TR_T_SON_I" AFTER
    INSERT ON t_son
    FOR EACH ROW
DECLARE
    sid INT; mid VARCHAR(99); ins INT; sub INT;  
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    SELECT :new.id,:new.main_id,:new.ins,:new.sub INTO sid,mid,ins,sub FROM t_son WHERE ROWNUM = 1;
 --   SELECT id,main_id,ins,sub INTO sid,mid,ins,sub FROM t_son where ROWNUM = 1;
 
    IF( sub <> '' ) THEN
        dbms_output.put_line('SID');
        print(sid);
        UPDATE t_son SET t_son.bal = ( SELECT ( t.bal - t_son.sub ) FROM t_main t WHERE id = mid ) WHERE t_son.id = sid;
        COMMIT;
    ELSE
        UPDATE t_son SET t_son.bal = ( SELECT ( t.bal + t_son.ins ) FROM t_main t WHERE id = mid ) WHERE t_son.id = sid;
        COMMIT;
   
    END IF;
        commit;
END;
/
ALTER TRIGGER "SYSTEM"."TR_T_SON_I" ENABLE;
--------------------------------------------------------
--  Constraints for Table T_MAIN
--------------------------------------------------------

  ALTER TABLE "SYSTEM"."T_MAIN" MODIFY ("ID" NOT NULL ENABLE);
 
  ALTER TABLE "SYSTEM"."T_MAIN" ADD CONSTRAINT "T_MAIN_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE;
--------------------------------------------------------
--  Constraints for Table T_SON
--------------------------------------------------------

  ALTER TABLE "SYSTEM"."T_SON" MODIFY ("ID" NOT NULL ENABLE);
 
  ALTER TABLE "SYSTEM"."T_SON" MODIFY ("MAIN_ID" NOT NULL ENABLE);
 
  ALTER TABLE "SYSTEM"."T_SON" ADD CONSTRAINT "T_SON_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE;

[heng_M]

想实现的目的就是，当t_son中的表记录发生变化的时候，判断如果是ins字段有值就根据main_id的值到t_main表中用bal值加上ins的值，并更新到t_son中的bal字段，如果是sub就执行减法操作

[heng_M]

引用 1 楼 yaiger 的回复:

select into 无记录会触发异常 t_son表有数据吗？

测试用空表

[nayi_224]

create or replace trigger "SYSTEM"."TR_T_SON_I" before
    insert on system.t_son
    for each row
begin

  select decode(:new.sub, null, t1.bal + :new.ins, t1.bal - :new.sub)
    into :new.bal
    from system.t_main t1
   where t1.id = :new.main_id;
   
end;

[yaiger]

既然没有数据，自然会触发异常，你的程序里面又没有exception处理，当然就不会执行

引用 3 楼 heng_M 的回复:

[quote=引用 1 楼 yaiger 的回复:]
select into 无记录会触发异常
t_son表有数据吗？

测试用空表[/quote]

[yaiger]

select into 无记录会触发异常
t_son表有数据吗？