Oracle triggers problem

hi to all,
i am creating the trigger for insert and update in oracle. the query is


create trigger insertandupdate
after update
on testtable
for each row
begin
insert into testtable1 values(:new.name,:new.age,:new.mailid,:new.datetime)

end;


the problem in that query is , when i am inserting one row , it is getting all the rows in the table including the newly inserted row. i want to get only the newly inserted row only not all the rows. how can i get the value. and how can i solve the problem. plz anybody know , help me.
thank u

with thanks and regards
mmary

Try this:

create trigger insertandupdate
after update
on testtable
for each row
begin
Select name,age,mailid,datetime Into
:new.NName,
:new.NAge,
:new.NMailid,
:new.NDatetime
From testtable
Where PK = :old.PK
insert into testtable1 values(:NName,NAge,:NMailid,:NDatetime);

end;

thank u very much for ur kind reply. but i am getting the following error



ERROR at line 2: PL/SQL: SQL Statement ignored


how can i solve the problem. plz help me


thank u
with thanks and regards
mmary

What are you actually tring to do here? The code looks like on an Update of the tabe you are going to update the same table with the same data?

thank u very much for ur help. i want to get the updated records from the testtable and i want to insert that updated records in the table1. and also i want to get the inserted records also. how can i do that. plz help me


thank u
with thanks and regards
mmary

You need two triggers on on insert the other on Update.

thank u very for ur help. i need the two trigges , one for insert and another one for update.

plz help me.
thank u

with thanks and regards
mmary

Here is the trigger

Code:

---

create or replace trigger testtableinsertupdate
after insert or update on testtabe
referencing New as n
For Each Row
Begin
  Insert Into Table1 (Name,Age,MailID,DateTime) Values (
      :n.Name,
      :n.Age,
      :n.MailID,
      :n.DateTime);
End;

---

I have tested this as follows:

1 created two tables:

Code:

---

  Create Table Testtable (
      Name varchar2(20),
      Age number,
      MailID varchar2(20),
      DateTime Date);
  Create Table Table1 (
      Name varchar2(20),
      Age number,
      MailID varchar2(20),
      DateTime Date);

---

2. Insert a row into testtable

Code:

---

Insert Into TestTable Values ('Gary',10,'gary','20-May-07');

---

3. Checked the data in table Table1 and saw the entered new row.
4. Inserted a new row into testtable

Code:

---

Insert Into TestTable Values ('Sam',11,'sam','21-May-07');

---

5. Check data in Table1
Saw two rows Gay and Sam
6. Updated a row in testtable

Code:

---

Update TestTable Set age = 50 Where Name = 'Gary";

---

7. Checked data in Table1
Saw Three rows 'Gay' at age 10, Sam and Gary at age 50

thank u. thank u very much .now its working fine. thank very much.


with thanks and regards
mmary

If this answers the question could you mark the thread as resolved.