表T1(C1,C2,C3),表T2(C1,C2,C3,c3,c4……)
触发器要求:
当对表T1新增一条记录时,调用触发器,将该记录插入T2;
当对表T1中的一条记录进行修改是,调用触发器,修改入T2中对应的记录;
当对表T1删除一条记录时,同时将T2中对应的记录也删除。
这样的触发器怎么写?
谢谢!
触发器要求:
当对表T1新增一条记录时,调用触发器,将该记录插入T2;
当对表T1中的一条记录进行修改是,调用触发器,修改入T2中对应的记录;
当对表T1删除一条记录时,同时将T2中对应的记录也删除。
这样的触发器怎么写?
谢谢!
after update or delete or insert
on t1
for each row
begin
if inserting then
insert into t2(C1,C2,C3) values(:new.C1,:new.C3,:new.C3);
elsif updating then
insert into t2(C1,C2,C3) values(:old.C1,:old.C3,:old.C3);
elsif deleting then
insert into t2(C1,C2,C3) values(:old.C1,:old.C3,:old.C3);
end if;
end;
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO T2 VALUES(:NEW.C1,:NEW.C2,:NEW.C3); ELSIF UPDATING THEN
UPDATE T2 SET T2.C1=:NEW.C1,T2.C2=:NEW.C2,T2.C3=:NEW.C3 WHERE T2.C1=:OLD.C1 AND T2.C2=:OLD.C2 AND T2.C3=:OLD.C3; ELSIF DELETING THEN
DELETE FROM T2 WHERE T2.C1=:NEW.C1;
END IF;
END;
--表T1(C1,C2,C3),表T2(C1,C2,C3,c3,c4……)
--假設C1為主鍵
--alter table T1 add constraint PK_T1 primary key (C1) USING INDEX;
--alter table T2 add constraint PK_T2 primary key (C1) USING INDEX;create or replace trigger T1_ins_upd_del after insert or update or delete on T1 for each row
begin
CASE
WHEN inserting THEN
INSERT INTO T2(C1,C2,C3) VALUES (:NEW.C1,:NEW.C2,:NEW.C3);
WHEN updating THEN
UPDATE T2
SET C2=NVL(:NEW.C2,:OLD.C2),C3=NVL(:NEW.C3,:OLD.C3)
WHERE C1=:OLD.C1;
WHEN deleting THEN
DELETE FROM T2 WHERE C1=:OLD.C1;
END CASE;
end;
請高手們指教。。