在a表上建立三个触发器,after insert/update/delete,分别处理三种类型的操作,使b表数据与a表相同,具体代码如下:--test table create table temp_a(id number,name varchar2(1000)); create table temp_b(id number,name varchar2(1000));--insert trigger create or replace trigger tr_temp_a_insert after insert on temp_a for each row begin insert into temp_b values(:new.id,:new.name); end;--update trigger create or replace trigger tr_temp_a_update after update on temp_a for each row begin update temp_b set id = :new.id, name = :new.name where id = :old.id and name = :old.name; end;--delete trigger create or replace trigger tr_temp_a_delete after delete on temp_a for each row begin delete from temp_b where id = :old.id; end;--insert test insert into temp_a values(1,'a'); insert into temp_a values(2,'b'); commit;select * from temp_a; select * from temp_b;--update test update temp_a set name = 'aa' where id =1; update temp_a set name = 'bb' where id =2; commit;select * from temp_a; select * from temp_b;--delete test delete from temp_a where id =1; delete from temp_a where id =2; commit;select * from temp_a; select * from temp_b;
不是很明白LZ的意思,不知道下面是不是你想要的CREATE TABLE a(id NUMBER,userName VARCHAR2(12),password VARCHAR2(8),birthday DATE); CREATE TABLE b AS SELECT * FROM a WHERE 1=2; --id不修改 CREATE OR REPLACE TRIGGER a_modify_trigger AFTER INSERT OR UPDATE OR DELETE ON a FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO b VALUES (:new.id, :new.userName, :new.password, :new.birthday); ELSIF UPDATING THEN UPDATE b SET userName = :new.userName, password = :new.password, birthday = :new.birthday WHERE id = :old.id; ELSE DELETE FROM b WHERE id = :old.id; END IF; END a_modify_trigger;
create table temp_a(id number,name varchar2(1000));
create table temp_b(id number,name varchar2(1000));--insert trigger
create or replace trigger tr_temp_a_insert
after insert on temp_a
for each row
begin
insert into temp_b values(:new.id,:new.name);
end;--update trigger
create or replace trigger tr_temp_a_update
after update on temp_a
for each row
begin
update temp_b set id = :new.id, name = :new.name where id = :old.id and name = :old.name;
end;--delete trigger
create or replace trigger tr_temp_a_delete
after delete on temp_a
for each row
begin
delete from temp_b where id = :old.id;
end;--insert test
insert into temp_a values(1,'a');
insert into temp_a values(2,'b');
commit;select * from temp_a;
select * from temp_b;--update test
update temp_a set name = 'aa' where id =1;
update temp_a set name = 'bb' where id =2;
commit;select * from temp_a;
select * from temp_b;--delete test
delete from temp_a where id =1;
delete from temp_a where id =2;
commit;select * from temp_a;
select * from temp_b;
CREATE TABLE b AS SELECT * FROM a WHERE 1=2;
--id不修改
CREATE OR REPLACE TRIGGER a_modify_trigger
AFTER INSERT OR UPDATE OR DELETE ON a
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO b
VALUES
(:new.id, :new.userName, :new.password, :new.birthday);
ELSIF UPDATING THEN
UPDATE b
SET userName = :new.userName,
password = :new.password,
birthday = :new.birthday
WHERE id = :old.id;
ELSE
DELETE FROM b WHERE id = :old.id;
END IF;
END a_modify_trigger;