Trigger with insert

[premkumarsp1]

hi
i created a trigger like this
[code=oracle]
create or replace trigger new before update on empl for each row
declare
pragma autonomous_tran saction;
begin
delete from empl1 where name = :old.name ;
insert into empl1 select * from empl where name=:old.name ;
commit;
end;
[/code]

If i do some updation in empl table that is not getting reflected due to this insert command. If i comment out the insert statement, trigger is working fine.
I need to specify some condition in the Insert statement thats why i cannot use INSERT into empl1 values(:new.nam e...) command
Wat could be the reason for this..can someone tell me the reason...

thanks in advance..

[debasisdas]

Why you need to delete and insert into the same base table on update ?

[QVeen72]

Hi,

Why do you need to mantain same set-of rows/Columns in both the tables empl1 and emp1...?

Regards
Veena

[premkumarsp1]

Why you need to delete and insert into the same base table on update ?

hi debasisdas
Actually i want to update a particular record. I dono which particular field will be updated. So, i am deleting the entire record based on the primary key and inserting back the new updated record.

thanks
prem

[premkumarsp1]

Hi,

Why do you need to mantain same set-of rows/Columns in both the tables empl1 and emp1...?

Regards
Veena

hi veena,
Empl1 is exactly similar in structure in empl. Wenever records are getting updated in empl, i want to update the empl1 table automatically. Empl and empl1 are used for different purposes.

[debasisdas]

hi veena,
Empl1 is exactly similar in structure in empl. Wenever records are getting updated in empl, i want to update the empl1 table automatically. Empl and empl1 are used for different purposes.

If that is the requirment why not create a materialized view on the base table.

[subashsavji]

[code=oracle] CREATE OR REPLACE TRIGGER NEW before UPDATE ON emp1 FOR each ROW
DECLARE
PRAGMA autonomous_tran saction;
BEGIN
DELETE FROM emp11 WHERE ename = :old.ename ;
INSERT INTO emp11 SELECT * FROM emp1 WHERE ename=:old.enam e ;
COMMIT;
END;


update emp1
set ename='parag'
where ename='taj';
[/code]
its working and showing.

[QVeen72]

Hi,

Just Create a View..

[code=oracle]
Create View empl1 as Select * From empl
[/code]

This way, your database size will also be kept in check..

REgards
Veena

[premkumarsp1]

hi all,
thanks for ur reply. I created a view. Its working fine. But there is a new problem. I created a trigger on a materialized view table similar to the existing problem. This time i am facing problem in the insert statement.

Table_mv is a materialized view table of another table which is in remote database.
table_ori is a table similar in structure to table_ori.
Requirement: whenever a record is inserted in table_mv table, the same record should be inserted in table_ori table.
[code=oracle]
create trigger t1 before insert on Table_mv for each row
DECLARE
pragma autonomous_tran saction;
v_msg4 varchar2(2000);
v_msg1 varchar2(2000);
v_msg2 varchar2(2000)
v_msg3 varchar2(2000);
v_msg5 varchar2(2000);
begin
IF inserting then
v_msg3 := 'Inserting in table_ori';
v_msg1 := :new.name;
v_msg2:=:NEW.ag e;
insert into table_ori as select * from table_mv where name=:new.name and age=:new.age;
commit;
v_msg4 := to_char(SQLCODE )
v_msg5:=substr( SQLERRM,1,200);
EXCEPTION
WHEN OTHERS THEN
v_msg4 :=to_char(SQLCO DE);
v_msg5:=substr( SQLERRM,1,200);
insert into table_log (name,age,place ,code,msg) VALUES (v_msg1,v_msg2, v_msg3,v_msg4,v _msg5);
commit;
end;
/
[/code]


The trigger is failing due to insert command. If i give Insert into table_ori values(:new.age ,.....) it is working fine. Moreover, as the insert command failed, the sql error code is not coming in table_log table.

I need to give insert command like insert into table_ori select * from table_mv
because i need to use where conditions.
Wat could be the reason for the insert statement command failure and why sql error code is not coming in table_log table?

can someone help me out...

thanks in advance...

[debasisdas]

If using a materialized view then why you need a trigger at all.

[premkumarsp1]

hi
table_mv is a materialized view of table table_rm.
table_rm is in remote database
Whenever a record is getting inserted in table_rm, it will get replicated in its materialixed view table_mv.
I want to insert the same record in table_ori. So, i wrote a trigger on table_mv.

regards
prem.