-
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.
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
jmodic My Hero
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;
-
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;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|