用触发器啊,如在Table中插入、更新、删除记录时,在TableB中插入相应的操作名称。 CREATE OR REPLACE TRIGGER TRG_R_INSPECT_REPORT BEFORE INSERT or delete or update ON TableA BEGIN if inserting then
insert into TableB(logid,logtable,logdml,logTIME) values (seq_LOG.nextval,'R_INSPECT_REPORT','insert',sysdate);
elsif deleting then
insert into TableB (logid,logtable,logdml,logTIME) values (seq_LOG.nextval,'R_INSPECT_REPORT','delete',sysdate);
else
insert into TableB (logid,logtable,logdml,logTIME) values (seq_LOG.nextval,'R_INSPECT_REPORT','update',sysdate);
end if; exception when others then raise_application_error(-20000,'error trg_test_b:' || SQLERRM); END TRG_R_INSPECT_REPORT; /
CREATE TABLE A001(ID INT PRIMARY KEY,NAME VARCHAR(20)) CREATE TABLE A002(ID INT REFERENCES A001(ID)ON DELETE CASCADE ON UPDATE CASCADE,AGE TINYINT)
楼上的错了吧。casecade 只有delete 关联删除这样 CREATE TABLE A001(ID INT PRIMARY KEY,NAME VARCHAR(20)); CREATE TABLE A002(ID INT ,foreign key(id) REFERENCES A001(ID) ON DELETE CASCADE ,AGE INT);但是楼主要自动修改只能用触发器实现了。 还用到上面的两个表 触发器如下: create or replace trigger tr_update before update on A001 for each row begin update a002 set id=:new.id where id=:old.id; end;
CREATE OR REPLACE TRIGGER TRG_R_INSPECT_REPORT
BEFORE INSERT or delete or update
ON TableA
BEGIN
if inserting then
insert into TableB(logid,logtable,logdml,logTIME) values (seq_LOG.nextval,'R_INSPECT_REPORT','insert',sysdate);
elsif deleting then
insert into TableB (logid,logtable,logdml,logTIME) values (seq_LOG.nextval,'R_INSPECT_REPORT','delete',sysdate);
else
insert into TableB (logid,logtable,logdml,logTIME) values (seq_LOG.nextval,'R_INSPECT_REPORT','update',sysdate);
end if;
exception
when others then
raise_application_error(-20000,'error trg_test_b:' || SQLERRM);
END TRG_R_INSPECT_REPORT;
/
关联删除这样
CREATE TABLE A001(ID INT PRIMARY KEY,NAME VARCHAR(20));
CREATE TABLE A002(ID INT ,foreign key(id) REFERENCES A001(ID) ON DELETE CASCADE ,AGE INT);但是楼主要自动修改只能用触发器实现了。
还用到上面的两个表
触发器如下:
create or replace trigger tr_update before update on A001 for each row
begin
update a002 set id=:new.id where id=:old.id;
end;