Trigger ...........When
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Trigger ...........When

  1. #1
    Join Date
    Aug 2001
    Posts
    134

    Lightbulb

    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.




  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  3. #3
    Join Date
    Aug 2001
    Posts
    134

    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;

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  


Click Here to Expand Forum to Full Width