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
Topic revision: r2 - 2006-03-15
- unknown