Trigger ...........When

[Jani]

I have created a trigger on a table like
*****************************************

create or replace trigger update_sal
before update on emp
for each row
when (new.sal < old.sal)
begin
raise_application_error (-20507 , 'Sorry ........');
end;

This trigger works Fine

But problem with this tigger
****************************

create or replace trigger insert_record
before insert on table
for each row
when (new.id = old.id)
begin
raise_application_error (-20007 , 'Sorry ......');
end;

I wanna create trigger.. Any Help
check the column value for duplication.
I know about the unique constraint and primary key.

[jmodic]

In INSERT row trigger you are checking the OLD value of the column! There is no :OLD value for the inserted record, as it didn't exist in the database until you inserted it.

[Jani]

I done this
****************

create or replace trigger insert_record
before insert on table
for each row
declare
v_dummy varchar2(1);
begin
select distinct count(id) into v_dummy from table
where id = :new.id;
if v_dummy > 0 then
raise_application_error (-20507 , 'Sorry .......');
end if;
end;

[jmodic]

The following variation is more efficent (particulary if your ID is not indexed), as it stops searching as soon as it finds the first occurence of the matching record (in your case it continues the search until there is no more matching records):

create or replace trigger insert_record
before insert on table
for each row
declare
v_dummy varchar2(1);
begin
select null into v_dummy from table
where id = :new.id
AND ROWNUM = 1;
raise_application_error (-20507 , 'Sorry .......');
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
end;