How The Driving Table is Determined

Previous Next

The driving table never depends on what the user does (the initial row event). It only depends on how the constraint SQL statement is formulated. For each constraint, the Rules Engine determines the driving table using the following rules:

•If the constraint is an UPDATE constraint, the driving table is the updated table.

•If the constraint is a DELETE constraint, the driving table is the table from which records are going to be deleted.

•If the constraint is an INSERT INTO... VALUES() constraint, the driving table is the table in which records are going to be inserted.

•If the constraint is an INSERT INTO... SELECT ... constraint, the driving table is identified as if only the SELECT clause was executed (see below).

•The following rules apply to restrictive constraints, and are ordered according to their priority:

1.If the constraint is transitional, the driving table is the transition table.
2.If the FROM clause contains a table for which all primary key columns appear in the statement, the driving table is the first-mentioned table in the FROM clause for which all primary key columns appear in the statement.
Usually this is the highest parent table that occurs within the constraint.
3.If no table occurs with all primary key columns mentioned, but the constraint does contain a GROUP BY clause, the driving table is the table that belongs to the first column in the GROUP BY clause.
4.In all other cases, the driving table is the first-mentioned table in the FROM clause.

NOTE: If the statement contains a RELATE clause, this is a placeholder for the normal WHERE clause expressing the relationship. The driving table is identified as if joined primary and foreign key column names appeared explicitly in the statement.

Example

Consider the business rule: "The time between the start date and return date of a scheduled tour must equal the preset number of days for the tour." This business rule is implemented by a restrictive constraint:

SELECT     '' violation

FROM       schedtour st

,          tour t

WHERE      t.destination = st.destination

AND        t.tour_type = st.tour_type

AND        t.num_days <> st.return_date - st.start_date + 1

 

The driving table of this constraint is TOUR, because only the TOUR table has all of its primary key columns mentioned in the statement.

Therefore, in the constraint test statement issued at commit time, row binding is applied to the TOUR table, independently of whether the original manipulation was on TOUR or on SCHEDTOUR:

SELECT     ''

FROM       OPS$CP1.SCHEDTOUR t1

WHERE   

(

           :1 = t1.DESTINATION

   AND     :2 = t1.TOUR_TYPE

   AND     TO_NUMBER(:3) <> t1.RETURN_DATE - t1.START_DATE + 1

)

(EUROPE,GRAND TOUR,28)

 

In this constraint test SQL statement:

•The TOUR table is not mentioned anymore. This is the consequence of applying row binding to the driving table.

In other words: only one record in the TOUR table matters, so values of this record can be substituted instead of mentioning the whole table.

•The number of days for the tour, 28 days, is also substituted in the constraint test. This value has been retrieved just before this constraint test was issued, when the driving table record was locked.