topic Re: create trigger in Operating System - HP-UX

create trigger

jonax — Fri, 12 May 2006 02:54:33 GMT

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.

Re: create trigger

Peter Godron — Fri, 12 May 2006 03:04:16 GMT

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

Re: create trigger

Indira Aramandla — Fri, 12 May 2006 03:38:52 GMT

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

Re: create trigger

jonax — Sun, 14 May 2006 21:12:29 GMT

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,

Re: create trigger

Frank de Vries — Mon, 15 May 2006 01:17:20 GMT

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,

Re: create trigger

Sunil Kumar MK — Mon, 15 May 2006 03:23:40 GMT

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

Re: create trigger

jonax — Mon, 22 May 2006 22:35:59 GMT

Thanks for helping me on resolving my errors.