好象不行,这样吧我把程序给你看看。当修改一个表时,触发此insert触发器 DECLARE NUM FLOAT(126); NUMDEP FLOAT(126); MENLIN FLOAT(126); MEN1 CHAR(1); MEN2 CHAR(1); MEN3 CHAR(1); NAM1 CHAR(20); NAM2 CHAR(20); NAM3 CHAR(20); ITE1 CHAR(30); ITE2 CHAR(30); ITE3 CHAR(30); VIS1 CHAR(5); VIS2 CHAR(5); VIS3 CHAR(5); begin NUM:=:NEW.emp;//当在此表中插入一条数据时,得到它的人员号,再根据此人员号查出下面一个“EMP_COD”表中的部门号放入变量“NUMDEP ”中。 MENLIN:=0; SELECT DEP INTO NUMDEP FROM EMP_COD WHERE UQI=NUM;//运行应用时在这就出错,说触发器不能读! WHILE MENLIN<=307 loop MENLIN:=MENLIN+1; select MEN_NAM1 into NAM1 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN; SELECT MEN_NAM2 into NAM2 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN; SELECT MEN_NAM3 into nam3 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN; SELECT MEN_ITE1 into ite1 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN; SELECT MEN_ITE2 into ite2 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN; SELECT MEN_ITE3 into ite3 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN; SELECT MEN_VIS1 into vis1 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN; SELECT MEN_VIS2 into vis2 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN; SELECT MEN_VIS3 into vis3 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN; MEN1:=substr(VIS1,1,1); MEN2:=substr(VIS2,1,1); MEN3:=substr(VIS3,1,1); INSERT INTO EMP_MEN VALUES(NUM,NUMDEP,MENLIN,MEN1,MEN2,MEN3,NAM1,NAM2,NAM3,ITE1,ITE2,ITE3,VIS1,VIS2,VIS3); end loop; END ;
你的触发器是针对哪个表的?如果是EMP_MEN ,就不能执行下面的插入语句 INSERT INTO EMP_MEN VALUES(NUM,NUMDEP,MENLIN,MEN1,MEN2,MEN3,NAM1,NAM2,NAM3,ITE1,ITE2,ITE3,VIS1,VIS2,VIS3);如果是针对EMP_COD的,只有before insert 触发器可以执行SELECT DEP INTO NUMDEP FROM EMP_COD WHERE UQI=NUM,而且只能是插入一条记录时。
是这样的,当emp_cod表中某一列修改时,插入一条数据到emp_log表! CREATE OR REPLACE TRIGGER "SYSTEM"."EMP_COD_UPD_LOG" BEFORE UPDATE OF "DEP", "FLA_LOG", "UQI" ON "EMP_COD" FOR EACH ROW DECLARE NUM INT; LOGa CHAR(1); BEGIN NUM:=:new.UQI; LOGa:=:new.FLA_LOG ; F LOGa='Y' then INSERT INTO EMP_LOG VALUES (NUM,'1123456789',0,0,0,sysdate,sysdate,NULL,0); ELSE DELETE FROM EMP_LOG WHERE EMP=NUM; end if; END; 当emp_log表被插入一条数据时,触发楼上insert触发器
你把下面的一段放到EMP_COD_UPD_LOG触发器中执行,NUMDEP:=:new.DEP,就不需要SELECT了SELECT DEP INTO NUMDEP FROM EMP_COD WHERE UQI=NUM;//运行应用时在这就出错,说触发器不能读! WHILE MENLIN<=307 loop MENLIN:=MENLIN+1; select MEN_NAM1 into NAM1 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN; SELECT MEN_NAM2 into NAM2 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN; SELECT MEN_NAM3 into nam3 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN; SELECT MEN_ITE1 into ite1 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN; SELECT MEN_ITE2 into ite2 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN; SELECT MEN_ITE3 into ite3 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN; SELECT MEN_VIS1 into vis1 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN; SELECT MEN_VIS2 into vis2 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN; SELECT MEN_VIS3 into vis3 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN; MEN1:=substr(VIS1,1,1); MEN2:=substr(VIS2,1,1); MEN3:=substr(VIS3,1,1); INSERT INTO EMP_MEN VALUES(NUM,NUMDEP,MENLIN,MEN1,MEN2,MEN3,NAM1,NAM2,NAM3,ITE1,ITE2,ITE3,VIS1,VIS2,VIS3); end loop;或者你可以在emp_log表中加入dep字段,这样就不用查询emp_cod表了,在触发器中是不能查询触发表的。
高手就是高手,我也是这样想的,我是在EMP_log表中加入dep字段,当emp_cod表中某一列修改时,插入一条数据到emp_log表中,在这个表的触发器中:NEW.DEP.想再问你一下,为什么ORACLE数据库中的触发器是不能查询触发表的。而在SQL SERVER 7。0中为什么可以呢,ORACLE我不是很懂,我以前是在SQL SERVER 中写触发器的。谢谢你!
DECLARE
NUM FLOAT(126);
NUMDEP FLOAT(126);
MENLIN FLOAT(126);
MEN1 CHAR(1);
MEN2 CHAR(1);
MEN3 CHAR(1);
NAM1 CHAR(20);
NAM2 CHAR(20);
NAM3 CHAR(20);
ITE1 CHAR(30);
ITE2 CHAR(30);
ITE3 CHAR(30);
VIS1 CHAR(5);
VIS2 CHAR(5);
VIS3 CHAR(5);
begin
NUM:=:NEW.emp;//当在此表中插入一条数据时,得到它的人员号,再根据此人员号查出下面一个“EMP_COD”表中的部门号放入变量“NUMDEP ”中。
MENLIN:=0;
SELECT DEP INTO NUMDEP FROM EMP_COD WHERE UQI=NUM;//运行应用时在这就出错,说触发器不能读!
WHILE MENLIN<=307 loop
MENLIN:=MENLIN+1;
select MEN_NAM1 into NAM1 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN;
SELECT MEN_NAM2 into NAM2 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN;
SELECT MEN_NAM3 into nam3 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN;
SELECT MEN_ITE1 into ite1 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN;
SELECT MEN_ITE2 into ite2 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN;
SELECT MEN_ITE3 into ite3 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN;
SELECT MEN_VIS1 into vis1 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN;
SELECT MEN_VIS2 into vis2 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN;
SELECT MEN_VIS3 into vis3 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN;
MEN1:=substr(VIS1,1,1);
MEN2:=substr(VIS2,1,1);
MEN3:=substr(VIS3,1,1);
INSERT INTO EMP_MEN VALUES(NUM,NUMDEP,MENLIN,MEN1,MEN2,MEN3,NAM1,NAM2,NAM3,ITE1,ITE2,ITE3,VIS1,VIS2,VIS3);
end loop;
END
;
INSERT INTO EMP_MEN VALUES(NUM,NUMDEP,MENLIN,MEN1,MEN2,MEN3,NAM1,NAM2,NAM3,ITE1,ITE2,ITE3,VIS1,VIS2,VIS3);如果是针对EMP_COD的,只有before insert 触发器可以执行SELECT DEP INTO NUMDEP FROM EMP_COD WHERE UQI=NUM,而且只能是插入一条记录时。
CREATE OR REPLACE TRIGGER "SYSTEM"."EMP_COD_UPD_LOG" BEFORE UPDATE OF "DEP", "FLA_LOG", "UQI" ON "EMP_COD" FOR EACH ROW DECLARE
NUM INT;
LOGa CHAR(1);
BEGIN
NUM:=:new.UQI;
LOGa:=:new.FLA_LOG ;
F LOGa='Y' then
INSERT INTO EMP_LOG
VALUES (NUM,'1123456789',0,0,0,sysdate,sysdate,NULL,0);
ELSE
DELETE FROM EMP_LOG
WHERE EMP=NUM;
end if;
END;
当emp_log表被插入一条数据时,触发楼上insert触发器
WHILE MENLIN<=307 loop
MENLIN:=MENLIN+1;
select MEN_NAM1 into NAM1 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN;
SELECT MEN_NAM2 into NAM2 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN;
SELECT MEN_NAM3 into nam3 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN;
SELECT MEN_ITE1 into ite1 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN;
SELECT MEN_ITE2 into ite2 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN;
SELECT MEN_ITE3 into ite3 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN;
SELECT MEN_VIS1 into vis1 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN;
SELECT MEN_VIS2 into vis2 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN;
SELECT MEN_VIS3 into vis3 FROM DEP_MEN WHERE DEP=NUMDEP AND MEN_LIN=MENLIN;
MEN1:=substr(VIS1,1,1);
MEN2:=substr(VIS2,1,1);
MEN3:=substr(VIS3,1,1);
INSERT INTO EMP_MEN VALUES(NUM,NUMDEP,MENLIN,MEN1,MEN2,MEN3,NAM1,NAM2,NAM3,ITE1,ITE2,ITE3,VIS1,VIS2,VIS3);
end loop;或者你可以在emp_log表中加入dep字段,这样就不用查询emp_cod表了,在触发器中是不能查询触发表的。