create or replace trigger testT AFTER INSERT OR UPDATE OR DELETE ON A for each row declare -- local variables here begin IF INSERTING THEN INSERT INTO b(a,b) VALUES(:NEW.a,:NEW.b); ELSIF DELETING THEN DELETE FROM b WHERE a=:OLD.a; ELSE UPDATE b SET b=:NEW.b WHERE a=:OLD.a; END IF; end testT;SQL> desc a Name Type Nullable Default Comments ---- ------------ -------- ------- -------- A VARCHAR2(20) Y B VARCHAR2(20) Y SQL> desc b; Name Type Nullable Default Comments ---- ------------ -------- ------- -------- A VARCHAR2(20) Y B VARCHAR2(20) Y SQL> insert into a values('a','b');1 row insertedSQL> commit;Commit completeSQL> select * from b;A B -------------------- -------------------- a bSQL> update a set b='c' where a='a';1 row updatedSQL> commit;Commit completeSQL> select * from b;A B -------------------- -------------------- a cSQL> delete from a where a='a';1 row deletedSQL> commit;Commit completeSQL> select * from b;A B -------------------- --------------------SQL>
谢谢大哥,我还有一个问题,若是字段很多时, INSERT INTO b(a,b) VALUES(:NEW.a,:NEW.b);还有uptate 会变得很长,有没有方法可以解决还是我就老老实实地这样一个个写呢 ?
AFTER INSERT OR UPDATE OR DELETE ON A
for each row
declare
-- local variables here
begin
IF INSERTING THEN
INSERT INTO b(a,b) VALUES(:NEW.a,:NEW.b);
ELSIF DELETING THEN
DELETE FROM b WHERE a=:OLD.a;
ELSE
UPDATE b SET b=:NEW.b WHERE a=:OLD.a;
END IF;
end testT;SQL> desc a
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
A VARCHAR2(20) Y
B VARCHAR2(20) Y SQL> desc b;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
A VARCHAR2(20) Y
B VARCHAR2(20) Y
SQL> insert into a values('a','b');1 row insertedSQL> commit;Commit completeSQL> select * from b;A B
-------------------- --------------------
a bSQL> update a set b='c' where a='a';1 row updatedSQL> commit;Commit completeSQL> select * from b;A B
-------------------- --------------------
a cSQL> delete from a where a='a';1 row deletedSQL> commit;Commit completeSQL> select * from b;A B
-------------------- --------------------SQL>
INSERT INTO b(a,b) VALUES(:NEW.a,:NEW.b);还有uptate
会变得很长,有没有方法可以解决还是我就老老实实地这样一个个写呢 ?