Trigger for capture the information [message #201821] |
Mon, 06 November 2006 22:18 |
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 |
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
|
|
|