Home » SQL & PL/SQL » SQL & PL/SQL » Trigger for capture the information
Trigger for capture the information [message #201821] Mon, 06 November 2006 22:18 Go to next message
kanmani_karuppannan
Messages: 23
Registered: November 2006
Location: India
Junior Member

Hi All,

I have a requirement like this

i have two table like

Table Name : a

Colums : x number,y number,z varchar2(50)

Table Name : b

Colums : x number,y number,z varchar2(50)


I need to create a trigger with following condition on table a

i) if i insert the records on table a, the same values will be stored into table b,

ii) if i delete the records on table a, deleted information shold be in table b

i,e Table a

x y z
100 50 xyz

if i delete where x = 100 then all corresponding information that should be stored into table b

i,e

x y z
100 50 xyz

Note : Column name for delete the records that will be selected randomly.

This all will be done on oracle 9i.

Please help me.

Thanks in Advance.


Regards,
Mani.
Re: Trigger for capture the information [message #201822 is a reply to message #201821] Mon, 06 November 2006 22:30 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

SQL> create table a ( col1 number, col2 number, col3 varchar2(10));

Table created.

SQL> create table b ( col1 number, col2 number, col3 varchar2(10));

Table created.
SQL> ed
Wrote file afiedt.buf

  1  create or replace trigger a_hist
  2  after insert or delete
  3  on a
  4  for each row
  5  begin
  6  if inserting then
  7  insert into b values (:new.col1,:new.col2,:new.col3);
  8  elsif deleting then
  9  insert into b values (:old.col1,:old.col2,:old.col3);
 10  end if;
 11* end;
SQL> /

Trigger created.

SQL> insert into a values (1,10,'aaa');

1 row created.

SQL> insert into a values (2,20,'bbb');

1 row created.

SQL> delete a where col1 = 2;

1 row deleted.

SQL> select * from a;

      COL1       COL2 COL3
---------- ---------- ----------
         1         10 aaa

SQL> select * from b;

      COL1       COL2 COL3
---------- ---------- ----------
         1         10 aaa
         2         20 bbb
         2         20 bbb

SQL>


hope this helps
Previous Topic: Calling External Java Program Not residing in Oracle
Next Topic: how to update a field in an insert trigger
Goto Forum:
  


Current Time: Sat Dec 07 02:27:53 CST 2024