CREATE table test1(
  COL1                     VARCHAR2(32),
  COL2                     NUMBER(12)
);

CREATE table test2(
  COL1                     VARCHAR2(64),
  COL2                     NUMBER(20)
);

CREATE table test3(
  COL1     NUMBER(20) not null  constraint pk_itest3 primary key,
  COL2     NUMBER(20),
  COL3     VARCHAR2(100)
);
insert into test3 values(0,1,'hello~1');
insert into test3 values(1,2,'hello~12');
insert into test3 values(2,2,'hello~123');

Here are some trggers for your reference...

• Example1 copy a record to test2 after insert into test1:

CREATE TRIGGER trig1
   AFTER INSERT ON test1
   FOR EACH ROW
   BEGIN
     INSERT INTO test2 VALUES(:new.col1, :new.col2);
   END trig1;
.
run;

• Example2: monitor column:COL1 in test1 after it is updated and COL2 > 100, copy it value to test2.COL1

CREATE TRIGGER trig2
   AFTER update of col1 on test1
   FOR EACH ROW
   BEGIN
     if (:new.col2 > 100) THEN
        update test2 set test2.col1=':new.col1' where test2.col2=:new.col2;
     END IF;
   END trig2;
.
run;

• Example 3: insert into test2 after test1 was inserted a record and if col2 of new record < 10. The col2 of test2 will be the same of test1 and the col1 of test2 will be always BIG. You can see that in this case, new record with col2 >= 10 will not insert anything to test2.

CREATE TRIGGER trig3
   AFTER INSERT ON test1
   FOR EACH ROW
   BEGIN
     IF (:new.col2 < 10) THEN
         INSERT INTO test2 VALUES('BIG', :new.col2);
     END IF ;
   END trig3;
.
run;

• How to run example trigger in lxplus:
  • Enable oracle setup in $HOME/.tcshrc or $HOME/.bashrc
  • Login to sqlplus # sqlplus CMS_DASHBOARD/password_here@devdb10

-- TaoShengCHEN - 15 Mar 2006