Oracle triggers problem

[mmary]

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

[GaryMazzone]

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 = ld.PK
insert into testtable1 values(:NName,NAge,:NMailid,:NDatetime);

end;

[mmary]

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

[GaryMazzone]

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?

[mmary]

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

[GaryMazzone]

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

[mmary]

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

[GaryMazzone]

Here is the trigger

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:

  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

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

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

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

[mmary]

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


with thanks and regards
mmary

[GaryMazzone]

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