create trigger

[jonax]

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.

[Peter Godron · Accepted solution]

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

[Indira Aramandla]

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

[jonax]

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]

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,

[Sunil Kumar MK]

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]

Thanks for helping me on resolving my errors.