Re: Check duplicate key in Forms 4.5

>Dear all,
>
> We are using Forms 4.5 and there is multi-record base block in
>one of
>our forms. Our users ocassionaly entered duplicate values in this block
>and the server error message will ONLY pop-up when the form is posted,
>
>Is there anyway to check PK uniqueness in multi-record block so as to
>alter user and stop him from further navigation.
>
>Your useful input is highly appreciated.
>
>Regards,
>Kevin
>
On a when-validate-row trigger, write a query that hits the database and checks for the violation. That will kinda work.

As a preamble to the following, we have a system-generated PK, but use Unique keys (UK) to enforce business rules.We have several tables where the UK includes such things as date, and the user may need to switch the dates on two rows. There is no easy way around this in forms because each changed row generates a SQL statement to update that row. When it changes the first row, it will conflict with the other, even though you and I know it will only be for a split second since the second one will be changed momentarily. One option is to force the user to put in dummy dates, and do a commit after each row, (UGLY). Another option is to delete one, update one, and insert a new one, then commit (UGLY). The other solution is, in the on-insert/on-update triggers to write the data to a transaction table that exactly matches the structure of the real table, but with an added column 'transaction_id', and an added column 'transaction_action'. The id is the same for all the current 'commit' for the form (set this in the on-commit trigger). The on-insert and on-update triggers write the
data to this transaction table, with the appropriate action. In the post-form-commit trigger, write 2 SQL statements... (pk is the real PK for simplicity, not the UK)

update real_table t1

   set (...) = (select ... from transaction_table t2

                    where t1.pk = t2.pk and
                     transaction_id = <whatever you set it to> and
                     transaction_action = 'UPDATE')

    t1.id in (select pk from transaction_table t2

                    where t1.pk = t2.pk and
                     transaction_id = <whatever you set it to> and
                     transaction_action = 'UPDATE')

This will update all the rows, and allow UKs to exist until the single SQL statement is completed. This allows you to flip-flop UKs. The other statement is the obvious...

insert into real_table

        select ... from  transaction_table t2
                    where
                     transaction_id = <whatever you set it to> and
                     transaction_action = 'UPDATE')  

(don't forget to cleanup...)

delete from transaction_table where

                     transaction_id = <whatever you set it to> and
                     transaction_action = 'UPDATE')  

(To be really complete, do some error trapping such that even if the insert/updates failed, the delete still gets done)

This sounds a lot more complicated than it really is, and it might be a bit tedious with all the column names, etc. AND it requires an entire extra table. However, now the user can change whatever row to whatever value, and only have to worry about it being correct when it they are ready to save.

Hope this helps...

Dan Received on Wed Mar 25 1998 - 00:00:00 CET