你定义的enq_flag char(1) not null, prn_flag char(1) not null, del_flag char(1) not null, upd_flag char(1) not null, prn_flag char(1) not null, 都是char(1),如果他的值是字母你怎么办? 还有如果prn_flag char(1) not null, del_flag char(1) not null, upd_flag char(1) not null, prn_flag char(1) not null 中有大于1的数字(做了转换后),也同样触发,但是这不是你希望看到的结果。
create table rightgrant( group_id number not null, unit_id number not null, enq_flag char(1) not null, add_flag char(1) not null, del_flag char(1) not null, upd_flag char(1) not null, prn_flag char(1) not null, primary key(group_id,unit_id) )create or replace trigger permTrig before update or insert on rightgrant REFERENCING NEW AS NEW_RIGHTGRANT FOR EACH ROW DECLARE i number :=0; enqFlag number :=0; BEGIN i:=TO_NUMBER(:NEW_RIGHTGRANT.ADD_FLAG)+TO_NUMBER(:NEW_RIGHTGRANT.UPD_FLAG)+TO_NUMBER(:NEW_RIGHTGRANT.DEL_FLAG)+TO_NUMBER(:NEW_RIGHTGRANT.PRN_FLAG); enqFlag:=TO_NUMBER(:NEW_RIGHTGRANT.ENQ_FLAG); IF i>0 AND enqFlag=0 THEN :NEW_RIGHTGRANT.ENQ_FLAG:='1'; END IF;end;
to db_zhang() : 谢谢你提的建议,我会改进它 to zhaoyongzhu(zhaoyongzhu) : 那我要怎么做,才能实现我说的功能呢?
参考一下 black_dragon(半仙)的吧。
奇怪,当我编译上述的语句的时候,自动就和oracle的通道断了,为什么???
SQL> create table rightgrant( 2 group_id number not null, 3 unit_id number not null, 4 enq_flag char(1) not null, 5 add_flag char(1) not null, 6 del_flag char(1) not null, 7 upd_flag char(1) not null, 8 prn_flag char(1) not null, 9 primary key(group_id,unit_id) 10 );Table created.SQL> commit;Commit complete.SQL> create or replace trigger permTrig 2 before update or insert 3 on rightgrant 4 REFERENCING NEW AS NEW_RIGHTGRANT 5 FOR EACH ROW 6 DECLARE 7 i number :=0; 8 enqFlag number :=0; 9 BEGIN 10 i:=TO_NUMBER(:NEW_RIGHTGRANT.ADD_FLAG)+TO_NUMBER(:NEW_RIGHTGRANT.UPD_FLAG)+TO_NUMBER(:NEW_RIGHTGRANT.DE L_FLAG)+TO_NUMBER(:NEW_RIGHTGRANT.PRN_FLAG); 11 enqFlag:=TO_NUMBER(:NEW_RIGHTGRANT.ENQ_FLAG); 12 IF i>0 AND enqFlag=0 THEN 13 :NEW_RIGHTGRANT.ENQ_FLAG:='1'; 14 END IF; 15 16 end; 17 /Trigger created.SQL> commit;Commit complete.
prn_flag char(1) not null,
del_flag char(1) not null,
upd_flag char(1) not null,
prn_flag char(1) not null,
都是char(1),如果他的值是字母你怎么办?
还有如果prn_flag char(1) not null,
del_flag char(1) not null,
upd_flag char(1) not null,
prn_flag char(1) not null
中有大于1的数字(做了转换后),也同样触发,但是这不是你希望看到的结果。
group_id number not null,
unit_id number not null,
enq_flag char(1) not null,
add_flag char(1) not null,
del_flag char(1) not null,
upd_flag char(1) not null,
prn_flag char(1) not null,
primary key(group_id,unit_id)
)create or replace trigger permTrig
before update or insert
on rightgrant
REFERENCING NEW AS NEW_RIGHTGRANT
FOR EACH ROW
DECLARE
i number :=0;
enqFlag number :=0;
BEGIN
i:=TO_NUMBER(:NEW_RIGHTGRANT.ADD_FLAG)+TO_NUMBER(:NEW_RIGHTGRANT.UPD_FLAG)+TO_NUMBER(:NEW_RIGHTGRANT.DEL_FLAG)+TO_NUMBER(:NEW_RIGHTGRANT.PRN_FLAG);
enqFlag:=TO_NUMBER(:NEW_RIGHTGRANT.ENQ_FLAG);
IF i>0 AND enqFlag=0 THEN
:NEW_RIGHTGRANT.ENQ_FLAG:='1';
END IF;end;
谢谢你提的建议,我会改进它
to zhaoyongzhu(zhaoyongzhu) :
那我要怎么做,才能实现我说的功能呢?
2 group_id number not null,
3 unit_id number not null,
4 enq_flag char(1) not null,
5 add_flag char(1) not null,
6 del_flag char(1) not null,
7 upd_flag char(1) not null,
8 prn_flag char(1) not null,
9 primary key(group_id,unit_id)
10 );Table created.SQL> commit;Commit complete.SQL> create or replace trigger permTrig
2 before update or insert
3 on rightgrant
4 REFERENCING NEW AS NEW_RIGHTGRANT
5 FOR EACH ROW
6 DECLARE
7 i number :=0;
8 enqFlag number :=0;
9 BEGIN
10 i:=TO_NUMBER(:NEW_RIGHTGRANT.ADD_FLAG)+TO_NUMBER(:NEW_RIGHTGRANT.UPD_FLAG)+TO_NUMBER(:NEW_RIGHTGRANT.DE
L_FLAG)+TO_NUMBER(:NEW_RIGHTGRANT.PRN_FLAG);
11 enqFlag:=TO_NUMBER(:NEW_RIGHTGRANT.ENQ_FLAG);
12 IF i>0 AND enqFlag=0 THEN
13 :NEW_RIGHTGRANT.ENQ_FLAG:='1';
14 END IF;
15
16 end;
17 /Trigger created.SQL> commit;Commit complete.