向各位oracle大牛们求救一个sql问题
我现在有三个表：
A表：
CREATE TABLE TABLE_A(
FORMNO VARCHAR2(20) PRIMARY KEY, --表单号
APP_DATE DATE, --申请日期
APP_USERID VARCHAR2(20) --申请人
)
B表：
CREATE TABLE TABLE_B(
FORMNO VARCHAR2(20) REFERENCES TABLE_A(FORMNO), --表单号
ITEMNO NUMBER PRIMARY KEY, --对应表单中的ITEM编号
CATEGORYNO VARCHAR2(20) PRIMARY KEY, --类别编号
UPDATETIME DATE --更新日期
)
B_HIST表：
CREATE TABLE TABLE_B_HIST(
FORMNO VARCHAR2(20) NOT NULL, --表单号
ITEMNO NUMBER NOT NULL, --对应表单中的ITEM编号
SEQ NUMBER NOT NULL, --序列号
CATEGORYNO VARCHAR2(20) NOT NULL, --类别编号
UPDATETIME DATE --更新日期
)
表的数据如下：
A表：
FORMNO APP_DATE APP_USERID
20130615001 20130615 SANDY
20130615002 20130615 MUSTARD
B表：
FORMNO ITEMNO CATEGORYNO UPDATETIME
20130615001 1 CA0001 20130615
20130615001 2 CA000B 20130615
20130615001 3 CA000W 20130615
20130615002 1 CA0001 20130615
B_HIST表：
FORMNO ITEMNO SEQ CATEGORYNO UPDATETIME
20130615001 1 1 CA0001 20130614
20130615001 2 1 CA000B 20130614
20130615001 3 1 CA000W 20130614
20130615001 1 2 CA0001 20130615
20130615001 2 2 CA000B 20130615
20130615001 3 2 CA000W 20130615
20130615002 1 1 CA0001 20130615
当我每次更新B表时，都会保存一条历史记录，并且每次的SEQ都会自动加一，这个触发器怎么写，求oracle大牛们帮个忙，非常感谢！！！ oracle?触发器?历史记录
[解决办法]
create or replace trigger mytrig after insert or delete or update on b
for each row
declare
c number;
begin
case
when inserting then
select count(1) + 1 into c from b_hist where formno = :new.formno and itemno = :new.itemno;
insert into b_hist values(:new.formno,:new.itemno,c,:new.categoryno,:new.updatetime);
when deleting then
select count(1) + 1 into c from b_hist where formno = :old.formno and itemno = :old.itemno;
insert into b_hist values(:old.formno,:old.itemno,c,:old.categoryno,:old.updatetime);
when updating then
select count(1) + 1 into c from b_hist where formno = :old.formno and itemno = :old.itemno;
insert into b_hist values(:old.formno,:old.itemno,c,:old.categoryno,:old.updatetime);
end case;
end;
SQL> select * from b;
FORMNO ITEMNO CATEGO UPDATETIM
------------ ---------- ------ ---------
20130615001 1 CA0001 15-JUN-13
20130615001 2 CA000B 15-JUN-13
20130615001 3 CA000W 15-JUN-13
20130615002 1 CA0001 15-JUN-13
SQL> select * from b_hist;
no rows selected
SQL> insert into b values('20130615003',1,'CA0007',to_date('20130617','yyyymmdd'));
1 row created.
SQL> select * from b_hist;
FORMNO ITEMNO SEQ CATEGORYNO UPDATETIM
------------ ---------- ---------- ---------- ---------
20130615003 1 1 CA0007 17-JUN-13
SQL> update b set categoryno = 'CA0001' where formno = '20130615003' and itemno = 1;
1 row updated.
SQL> select * from b_hist;
FORMNO ITEMNO SEQ CATEGORYNO UPDATETIM
------------ ---------- ---------- ---------- ---------
20130615003 1 1 CA0007 17-JUN-13
20130615003 1 2 CA0007 17-JUN-13
SQL> delete from b where formno='20130615003' and itemno = 1;
1 row deleted.
SQL> select * from b_hist;
FORMNO ITEMNO SEQ CATEGORYNO UPDATETIM
------------ ---------- ---------- ---------- ---------
20130615003 1 1 CA0007 17-JUN-13
20130615003 1 2 CA0007 17-JUN-13
20130615003 1 3 CA0001 17-JUN-13
SQL>
