USoft Rule Language examples: Insert statements

The following example illustrates the use of the USoft Rule Language ADD WHERE NOT EXISTS keyword in an insert statement, as used for corrective constraints.

NOTE: The construct with ADD WHERE NOT EXISTS is much more compact than its SQL Equivalent, because it implies the WHERE condition and subquery that check that the record to be inserted does not already exist.

The disadvantage of ADD WHERE NOT EXISTS is that it will only let you write INSERT statements that perform this check. This is a slight disadvantage, since the vast majority of corrective INSERT constraints require this check.

USoft Rule Language

SQL Equivalent

FOR            participants
(
               reservation_id
,              person_id
)
ADD WHERE NOT EXISTS
SELECT         r.reservation_id
,              p.person_id
FROM           reservation r
,              person p
WHERE          condition

INSERT INTO participant
(
             reservation_id
,            person_id
)
SELECT
             r.reservation_id
,            p.person_id
FROM         reservation r
,            person p
WHERE NOT EXISTS
(
        SELECT       ''
        FROM         participant pt
        WHERE        pt.res_id = r.res_id
        AND          pt.person_id = p.person_id
)
AND     condition