表A 字段﹕A,y(年)﹐M(月);
表B 字段﹕B,y(年)﹐M(月);
建一觸發﹕當表A中有數據插入時﹐將該數據
M<12時﹐M=M+1,Y=Y;A不變。
M=12時﹐M=1 ,Y=Y+1;A不變。插入到表B中。
覆蓋表B中Y,M相等的數據。
表B 字段﹕B,y(年)﹐M(月);
建一觸發﹕當表A中有數據插入時﹐將該數據
M<12時﹐M=M+1,Y=Y;A不變。
M=12時﹐M=1 ,Y=Y+1;A不變。插入到表B中。
覆蓋表B中Y,M相等的數據。
急﹗急﹗急﹗急﹗急﹗
SQL> create table a(y int,m int);Table createdSQL> create table b(y int,m int);Table createdSQL> create or replace trigger trg_insertb
2 After insert on a for each row
3 declare
4 v_m int;
5 v_y int;
6 begin
7 dbms_output.put_line(:new.Y);
8 if (:new.m<12) then
9 v_m:=:new.M+1;
10 v_y:=:new.Y;
11 elsif (:new.m=12) then
12 v_m:=1;
13 v_y:=:new.Y+1;
14 end if;
15 insert into b values(v_Y,v_M);
16 end;
17 /Trigger createdSQL> insert into a values(2008,8);20081 row insertedSQL> insert into a values(2008,12);20081 row insertedSQL> select * from a; Y M
--------------------------------------- ---------------------------------------
2008 8
2008 12SQL> select * from b; Y M
--------------------------------------- ---------------------------------------
2008 9
2009 1SQL>
DROP TABLE b;
CREATE TABLE a (a INT,y INT,m INT);
CREATE TABLE b (b INT,y INT,m INT);
CREATE OR REPLACE TRIGGER TRIG_A
AFTER INSERT ON A
FOR EACH ROW
DECLARE
l_y INT;
l_m INT;
L_countb INT;
BEGIN
IF :new.m<12 THEN
l_y:=:new.y;
l_m:=:new.m+1;
ELSIF :new.m=12 THEN
L_m:=1;
l_y:=:new.y+1;
END IF;
SELECT COUNT(*) INTO l_countb FROM b WHERE y=L_y AND m=l_m;
IF l_countb>0 THEN
UPDATE b SET b=:new.a WHERE y=L_y AND m=l_m;
ELSE
INSERT INTO b VALUES(:new.a,l_y,l_m);
END IF;
END;
/SELECT * FROM b;
INSERT INTO a VALUES(1,1,1);
INSERT INTO a VALUES(1,1,1);
INSERT INTO a VALUES(2,2,2);
INSERT INTO a VALUES(12,12,12);
只要去插入的值先判断在插入
既可以在程序端判断也可以在SQL中写!
l_y:=:new.y;
l_m:=:new.m+1;
ELSIF :new.m=12 THEN
L_m:=1;
l_y:=:new.y+1;
END IF;
SELECT COUNT(*) INTO l_countb FROM b WHERE y=L_y AND m=l_m;
插入的數據列的年月在表中都改變了你再修改它插入時的年月???
IF l_countb>0 THEN
UPDATE b SET b=:new.a WHERE y=L_y AND m=l_m;
ELSE
INSERT INTO b VALUES(:new.a,l_y,l_m);
END IF;
CREATE TABLE b (b INT,y INT,m INT);二、先在b表中建立个主关键字:
alter table b add constraint PK_b primary key(y,m);三、4楼的触发器做的也不错(改进了表B中的覆盖):
CREATE OR REPLACE TRIGGER TRIG_A
AFTER INSERT ON A
FOR EACH ROW
DECLARE
l_y INT;
l_m INT;
L_countb INT;
BEGIN
IF :new.m<12 THEN
l_y:=:new.y;
l_m:=:new.m+1;
ELSIF :new.m=12 THEN
L_m:=1;
l_y:=:new.y+1;
END IF;
SELECT COUNT(*) INTO l_countb FROM b WHERE y=L_y AND m=l_m;
IF l_countb>0 THEN
UPDATE b SET b=:new.a WHERE y=L_y AND m=l_m;
ELSE
INSERT INTO b VALUES(:new.a,l_y,l_m);
END IF;
END;