Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

create trigger

SOLVED
Go to solution
jonax
Occasional Contributor

create trigger

Does anyone knows how to create trigger in oracle8i that will papulate table2(temporary table)
when table1(live table) updates/changes rows done by the end user.
Can you give me specific example on how to create this trigger.

thanks you.


6 REPLIES
Peter Godron
Honored Contributor
Solution

Re: create trigger

Jonax,
create trigger ledger_audit
before update on table1
for each row
begin
insert into table2 values
( :new.data1, :old.data2);
end;
Indira Aramandla
Honored Contributor

Re: create trigger

Hi jonax,

You can create a trigger that execute
implicitly when an INSERT, UPDATE, or DELETE statement is issued against
the associated table.

A trigger can be fired upon the execution of a SQL statement or by an event.

There are BEFORE triggers and AFTER triggers.
You require an AFTER trigger.

With an AFTER ROW trigger. -- Trigger action is executed after modifying each row and possibly applying appropriate integrity constraints (rows are locked).

Peter has given you the syntax above.


IA

Never give up, Keep Trying
jonax
Occasional Contributor

Re: create trigger

Hi peter,

Below are my sample tables.

Im not sure if table2 is correct.

what do you mean by :new.data1, :old.data2 ?? is it a new/old columns in table2?



table1

CREATE TABLE TABLE1
(
POLNUM VARCHAR2(15 BYTE),
COVNUM NUMBER(4)
);


table2

CREATE TABLE TABLE2
(
NEW_POLNUM VARCHAR2(15 BYTE),
POLNUM VARCHAR2(15 BYTE),
NEW_COVNUM NUMBER(4),
COVNUM NUMBER(4)
);


thank you,
Frank de Vries
Respected Contributor

Re: create trigger

Hope this will help,
this will do an insert on table2 after an insert on table1.

Mind you the insert on table2 is very basic,
to give you an example. We just count a nextval

CREATE OR REPLACE TRIGGER table12_insert AFTER INSERT ON table1 FOR EACH ROW
begin
INSERT INTO table2( new_polnum, polnum, new_covnum, covnum ) VALUES ( new_polnum.NEXTVAL, 'table12_insert', :new.new_covnum);
end;
/


regards,
Look before you leap
Sunil Kumar MK
Occasional Advisor

Re: create trigger

Hi Jonax,

:new.col_name refers to the new value being inserted into column "col_name" and :old.col_name refers to the existing value thats being overwritten or updated.

Hope this clarifies.

Best Regards
Sunil
jonax
Occasional Contributor

Re: create trigger

Thanks for helping me on resolving my errors.