Re: SQL*Forms 2.3 PRE-INSERT and PRE-UPDATE Triggers

skl1_at_Ra.MsState.Edu (Soo K. Lee) writes:

> I am having a problem in inserting a new record in a block. This block is
> single row block but has one to many relationship with master block.
>
> The Form must behave like this:
>
> 1) When a user tries to create new records,
> a field called effective_date has to be the latest among existing
> records' effective_dates.
>
> 2) When a user tries to update an existing record,
> if the effective_date is the same date as the previous date in the record,
> then update the existing record directly
> otherwise insert a brand new record with the new effective_date.
> (of course this date has to be the latest.)

Assuming that the detail block contains the field EFFECTIVE_DATE and the field KEY (which would tie the detail records to their associated master record), you could use these PRE-INSERT and PRE-UPDATE triggers:

PRE-INSERT trigger

        Step#1

        If there are detail records for the current entity (i.e., the current
        value of KEY) whose EFFECTIVE_DATE is later than that on the record
        being inserted then the insert should be aborted.

        SELECT  1
        FROM    DETAIL_TABLE
        WHERE   DETAIL_TABLE.EFFECTIVE_DATE > :DETAIL_BLOCK.EFFECTIVE_DATE
        AND     DETAIL_TABLE.KEY = :DETAIL_BLOCK.KEY

        Set the step's success/failure condition to fail if any rows are
        retrieved.

PRE-UPDATE trigger

        Step#1

        We insert a row into DETAIL_TABLE if the current record's date field
        has a different value to that stored in the database table.

        INSERT  INTO DETAIL_TABLE (KEY, EFFECTIVE_DATE, ...)
        SELECT  :DETAIL_BLOCK.KEY, :DETAIL_BLOCK.EFFECTIVE_DATE, ...
        FROM    DETAIL_TABLE
        WHERE   DETAIL_TABLE.ROWID = :DETAIL_BLOCK.ROWID
        AND     DETAIL_TABLE.EFFECTIVE_DATE <> :DETAIL_BLOCK.EFFECTIVE_DATE

        Set the step's success/failure condition to succeed even if no
        rows are inserted.

        Step#2

        Since we can't prevent Forms V2 from performing an update when a record
        is flagged as changed, if we don't want an update to take effect we
        retrieve the current record's data from the database and overwrite the
        values which the user typed into the form.  The update will thus write
	the old data back into the database and the net effect is no change.
	NOTE: I haven't tried this but I think it should work.

	SELECT	DETAIL_TABLE.KEY, DETAIL_TABLE.EFFECTIVE_DATE, ...
	INTO	:DETAIL_BLOCK.KEY, :DETAIL_BLOCK.EFFECTIVE_DATE, ...
        FROM    DETAIL_TABLE
        WHERE   DETAIL_TABLE.ROWID = :DETAIL_BLOCK.ROWID
        AND     DETAIL_TABLE.EFFECTIVE_DATE <> :DETAIL_BLOCK.EFFECTIVE_DATE

        Set the step's success/failure condition to succeed even if no
        rows are retrieved.

Hope this helps.

David Hazledine                                                EMBL Data Library
Database Administrator                                                PF 10.2209
EMBL Data Library                                       6900 Heidelberg, Germany

Internet: Hazledine_at_EMBL-Heidelberg.DE