www.usoft.com To www.usoft.com
Question

Adding a default record when a another record is inserted

  • 18 April 2024
  • 1 reply
  • 13 views
B

Anyone here who can show me the right direction to achieve the following ? I try to explain it clearly. 

 

Table Aircraft_types has the columns pattern_id, aircraft_id, version_nr, aircraft_class )


When someone insert a new record in this table Aircraft_types, a default record need to be inserted into the same Aircraft_types table. 
it's important that this new record has the same value for Aircraft_id and pattern_id. the aircraft_class need to be retrieved from the column Classid from the aircraft_pattern based on the new pattern_id
and it's also important that the default record doesn't need to be created when there is already a record where pattern_id and aircraft_id are identical.

So for example
somene enters the record
pattern_id = BE9M and aircraft_id = P47.

The default record that has to be created is
pattern_id  = BE9M 
aircraft_id = BE9M
aircraft_class = must contain the value of this select : select aircraft_class from aircraft_patterns where pattern_id = 'BE9M'

 

I've tried a constraint and a decision ( that is called in the post-insert trigger in the application), but with no success. 

the constraint:

INSERT INTO AIRCRAFT_TYPES ( aircraft_id, pattern_id, aircraft_class )
    SELECT cap.pattern_id, cap.pattern_id, cap.category
      FROM AIRCRAFT_PATTERNS cap
   WHERE NOT EXISTS ( SELECT ''
                      FROM AIRCRAFT_TYPES cat
                     WHERE cat.version_nr = cap.version_nr
                       AND cat.pattern_id = cap.pattern_id
                       AND cat.pattern_id = cat.AIRCRAFT_D)

The deicsion was similar, but then with using the sql-script() function.

1 reply

R
Userlevel 2
Badge +2

Dear Bert,

As this is a Business Rule for your application, I would use a constraint. I formulated the Business Rule thus:
"When some adds an aircraft type, a default aircraft type must be added if it is not yet available."

From your SQL I assume that the primary key of Aircraft Patterns consists of the pattern ID and the version number.
I also  assume you defined a relationship between Aircraft Types and Patterns, so Pattern ID and version number in Aircraft Types refer to the Patterns.
Last I assume Aircraft ID completes the primary key of Aircraft Types.

Because you wrote "When someone inserts a new record ..." I would make this constraint transitional:
Fire on Insert: Always
Fire on Delete: Never
Fire on Update" Used columns

I quickly built a reference model. I think from there you can create a working solution for you application (I abbreviated Aircraft to AC).

The constraint SQL:

insert into    AC_TYPE
( pattern_id
, version_nr
, aircraft_id
, aircraft_class
)
select
    acpn.pattern_id
,    acpn.version_nr
,    acpn.pattern_id    /* aircraft_id */
,    acpn.category    /* aircraft_class */
from
    AC_PATTERN acpn
,    AC_TYPE acte
relate
    acpn "FOR" acte
where
    NOT exists
    (    select 'default'
        from
            AC_TYPE tdef
        where
            tdef.pattern_id = acpn.pattern_id
        and
            tdef.version_nr = acpn.version_nr
        and
            tdef.aircraft_id = acpn.pattern_id
        and
            tdef.aircraft_class = acpn.category
    )
and
    acpn.category != acte.aircraft_class
and
    acpn.pattern_id != acte.aircraft_id
Constraint from Definer

In the application I tested this by adding two types. The first also added the default, the second did not:
 

One pattern, three types

hope this helps.

 

Regards,

Robert

Reply


Terms & ConditionsCookie settings