All Forums Database
kunokb 14 posts Joined 08/11
28 Oct 2011
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? :)

kunokb 14 posts Joined 08/11
30 Oct 2011

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...

You must sign in to leave a comment.