Problem with enforcing TEMPORAL RI with triggers in TD 13.10...

Database
Enthusiast

Problem with enforcing TEMPORAL RI with triggers in TD 13.10...

I've found an appendix at the end of TD 13.10's "Temporal Table Support" reference, which contains some SQL code about how can you validating temporal referential integrity shown below. It works great until I want to embed it to a trigger's ...WHEN ( EXISTS (<here>))... section. Because this section don't tolerate the WITH clause - as I see - unfortunately...

WITH SUBTBL(x,d) AS (NONSEQUENCED VALIDTIME SELECT pk,vt FROM tpar)
SELECT * FROM (
NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME
SELECT fk,tchld.vt
FROM TABLE (
TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d) RETURNS (pk1 INTEGER, vtp1 PERIOD(DATE))
HASH BY x LOCAL
ORDER BY x,d) AS TBLF(_pk,vtp), tchld
WHERE
_pk=fk AND
NOT (tblf.vtp CONTAINS tchld.vt) AND
tchld.vt IS NOT NULL
UNION
SELECT * FROM (
NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME
SELECT fk,tchld.vt FROM tchld
WHERE
fk NOT IN (SELECT pk FROM tpar) AND
fk IS NOT NULL)
DT)
DT(x,y) order by 1, 2;

If this query has resulting rows, the temporal RI is violated. It also contains a coalescing method, etc... And this is the BiT parent - VT child case with SRI, but it's unnecessary, actually.

What can I do? I had to express this functionality without this WITH clause? Hmm...

(It is also interesting, that why temporal validating couldn't be implemented in TD 13.10... They were in hurry or what? :)

1 REPLY
Enthusiast

Re: Problem with enforcing TEMPORAL RI with triggers in TD 13.10...

If I'm trying to replace the <here> section with the code above, I get the following message:


 *** Failure 3707 Syntax error, expected something like a name or a Unicode

 delimited identifier or a 'SELECT' keyword or '(' or a 'NONTEMPORAL' keyword

 or 'AS' keyword between '(' and the 'WITH' keyword.

The next problem is, that I can't substitute the WITH clause with a conventional SELECT statement (which works in normal), because the TD_NORMALIZE_MEET function requires the WITH caluse to it's parameters...

This function requires the WITH clause, but the WITH clause is undesirable in any SELECT statement...