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)
);

  • 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;

  • 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 | Raw edit | More topic actions...
Topic revision: r1 - 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-2021 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback