17,377
社区成员
发帖
与我相关
我的任务
分享
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;
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;