These 2 example triggers use 2 tables:

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

Edit | Attach | Watch | Print version | History: r2 < r1 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r2 - 2006-03-15 - unknown
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    LCG All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2019 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback