Re: Problems with the On-Insert trigger (Forms)

"Luis Cabral" <luiscabral_at_starmedia.com> wrote in message news:8ntp6l$4pe$1_at_nnrp1.deja.com...
> Maybe on-insert should only be used against non-oracle databases.

Works great against Oracle databases.

An example is where you want the user to have an entry-only screen (don't query existing data). When the user commits, use the ON-INSERT trigger to override the standard default commit processing logic so that you can update a record if one exists, but continue to insert a record if one doesn't. Example:

declare

    cursor check_for_rec is
    select <columns>
    from <table>
    where <unique key attributes>
    for update of <column1, column2...>;     check_for_rec_record check_for_rec%ROWTYPE; begin

    open check_for_rec;
    fetch check_for_rec into check_for_rec_record;     if check_for_rec%found then

        update <table>
        set column1 = :myblock.column1,
        column2 = :myblock.column2,
        (etc...)
        where current of check_for_rec;
        close check_for_rec;
    else
        close check_for_rec;
        --No existing record so do the normal default insert that Forms would
have done
        insert_record;

    when others then

        if check_for_rec%isopen then
            close check_for_rec;
        end if;
        message('Uh-oh!');

ON-INSERT is for overriding the standard internal DMLs that the form generates. PRE-INSERT is for lots of things, but most likely it's for things like calculating other database fields on the record prior to insert, setting default values for non-null columns, etc.

ON-UPDATE and PRE-UPDATE are probably similar, but are for update statements (duh).

-Matt Received on Wed Aug 23 2000 - 06:05:58 CEST