Trigger to delete a record

[dearsaran]

Hi all,
I've created a trigger as below.. my requirement is as follows... if i insert a record in emp with id=100 and if that particular id(100) is present in the employee table then that record has to be deleted...

but with my trigger all the records are getting deleted from the other table...

table description is same for both the tables::

empid varchar2(5)
empname varchar2(15)


CREATE OR REPLACE TRIGGER emp_trig
after insert
on emp
begin
delete from employee where empid=empid;
end;
/


please help me out in the following.............

[debasisdas]

that is because you are not checking whether that value already exists in the table or not in your code.

[AdusumalliGopikumar]

Hello ,
This is your trigger Stored procedure ...

CREATE OR REPLACE TRIGGER emp_trig
after insert
on emp
begin
delete from employee where empid=empid;
end;


After firing this trigger all the values get deleted in that table ,

The reason is because you have given the where condition as empid=empid;

so every empid in that table is checked with itself and the condition is always true for all empid's . so , every thing gets deleted..

[holdingbe]

hi,
I executed the this scripts but the oracle server shows the compilation errors because empid is invalid identifier.....please you write like :new.empid
please you study the :new and :old opertor.....



thanks and regards
Michael

[Saii]

to check whether new value is same as old use condition if :old.<value> = :new.<value>