create table user_file (
userID int not null,
userName VARCHAR2(10),
userPwd VARCHAR2(10),
primary key (userID)
);create table articletype (
magazineType int not null,
magazienTypeName VARCHAR2(20) not null,
primary key (magazineType)
);create table article (
authorID int not null,
artcleName VARCHAR2(20) not null,
reportDate DATE,
magazine VARCHAR2(20),
magazineType int,
authorOrder int,
extend VARCHAR2(100),
foreign key (authorID) references user_file(userID)on delete cascade,
foreign key (magazineType) references articletype(magazineType) on delete cascade
);create table log_file (
userID int not null,
operater VARCHAR2(10),
operaterDest VARCHAR2(20),
operaterDate DATE,
foreign key (userID) references user_file(userID)
);
前面是我建的表,下面是我建的触发器
--论文更新触发器
CREATE OR REPLACE TRIGGER article_log_file
BEFORE INSERT OR UPDATE OR DELETE ON article
FOR EACH ROW
BEGIN
IF UPDATING THEN
insert into log_file values(:old.authorID,'update',:old.artcleName,sysdate);
ELSIF DELETING THEN
insert into log_file values(:old.authorID,'delete',:old.artcleName,sysdate);
ELSIF INSERTING THEN
insert into log_file values(:new.authorID,'insert',:new.artcleName,sysdate);
END IF;
END;--用户更新触发器
CREATE OR REPLACE TRIGGER user_file_log_file
BEFORE INSERT OR UPDATE OR DELETE ON user_file
FOR EACH ROW
BEGIN
IF INSERTING THEN
insert into log_file values(:new.userID,'insert','用户信
息',sysdate);
ELSIF DELETING THEN
insert into log_file values(:old.userID,'delete','用户信
息',sysdate);
END IF;
END;--期刊类型日志触发器
CREATE OR REPLACE TRIGGER articletype_log_file
BEFORE INSERT OR UPDATE OR DELETE ON articletype
FOR EACH ROW
BEGIN
IF UPDATING THEN
insert into log_file values(:old.magazineType,'update','期刊类型',sysdate);
ELSIF DELETING THEN
insert into log_file values(:old.magazineType,'delete','期刊类型',sysdate);
ELSIF INSERTING THEN
insert into log_file values(:new.magazineType,'insert','期刊类型',sysdate);
END IF;
END;当我添加、删除用户及添加期刊类型时,数据库老出错,我就不明白了,操作/错误如下:
insert into user_file values(1001,'meixqhi','880312a');
insert into articletype values(1,'现代文学');错误:
insert into user_file values(1001,'meixqhi','880312a')
*
ERROR 位于第 1 行:
ORA-02291: 违反完整约束条件 (SYSTEM.SYS_C002692) - 未找到父项关键字
ORA-06512: 在"SYSTEM.USER_FILE_LOG_FILE", line 3
ORA-04088: 触发器 'SYSTEM.USER_FILE_LOG_FILE' 执行过程中出错我把触发器DROP掉后再插入,然后再创建,最后删除,有报如下错误: ORA-02292: 违反完整约束条件 (SYSTEM.SYS_C002681) - 已找到子记录日志。难道我的外键约束有问题,我想不明白?
(积分用完了,害我一个问题发了两遍,重新注册了号)
解决方案 »
- 安装TOAD后,在命令窗口导出数据失败
- 客户端如何配置监听?
- oracle的几道选择题 帮我选下 谢谢
- PowerDesigner 中如何将同义词授权给PUBLIC
- 【200分】一个包含日期条件的存储过程,日期对象该如何处理??如果可以马上结贴!!!!!!!!
- 猎头职位,谢谢大家关注,数据库开发工作程的职位!!!
- Oracle8.1.7.4补丁不能安装?
- 将Mysql中的一数据库转到Oracle中,写blob字段时出现的问题。
- 一个需求设计问题?有点难度!早就听说csdn里高手云集。那么高手请进。。。
- 输入一个日期,如何把这一日期所在的季度全部数据查出来?
- 求高手解答oralce合并两个表的数据问题
- oracle安装在小型机上CPU利用率居高不下
对外键和触发器使用要谨慎,触发器不易调试,使用太多易造成混乱。
那你把
insert into user_file values(1001,'meixqhi','880312a'); commit;
insert into articletype values(1,'现代文学'); commit;每个后面都加个 commit试试
insert into user_file values(1001,'meixqhi','880312a');第二句:
insert into articletype values(1,'现代文学');
articletype插入一条,
但是
-------------------------------------------
CREATE OR REPLACE TRIGGER articletype_log_file
after INSERT OR UPDATE OR DELETE ON articletype
FOR EACH ROW
BEGIN
IF UPDATING THEN
insert into log_file values(:old.magazineType,'update','期刊类型',sysdate);
ELSIF DELETING THEN
insert into log_file values(:old.magazineType,'delete','期刊类型',sysdate);
ELSIF INSERTING THEN
insert into log_file values(:new.magazineType,'insert','期刊类型',sysdate);
END IF;
END;
-------------------------------------------
触发器中向log_file插入,但外键指向foreign key (userID) references user_file(userID),
user_file中没有userID=1,所以报错!