Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5763

GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files

    Details

    • Type: Bug
    • Status: Resolved (View Workflow)
    • Priority: Critical
    • Resolution: Done
    • Affects Version/s: 12.2
    • Fix Version/s: 12.3
    • Component/s: None
    • Labels:
      None

      Description

      The issue arrised in:

      https://developer.jboss.org/message/989700#989700

      Following sceanarios are given:

      Variant 1:

      Table Product(id), primary key = autoincrement;
      Table QuicklyAddedProduct, primary key is foreign key on Product.id
      Table Diary_SRC
      View Diary on Diary_SRC

      INSTEAD OF INSERT Trigger on Diary should :
      -create new record on Product,
      -get autoincremented Product.id of new record,

      • create new QuicklyAddedProduct with returned Product.id as primary key
      • create new Diary record with QuicklyAddedProduct { INSERT a new record on Product; idProduct = CONVERT(GENERATED_KEY('idProduct'),long); // fails }

      Variant 2:
      INSTEAD OF INSERT trigger on Diary should :

      • create new QuicklyAddedProduct
      • add Quickly added prodcut to Diary

      additionally a INSTEAD OF trigger on QuicklyAddedProduct exists which:

      • creates new Product record
      • uses returned GENERATED_KEY(Product.id) to add record on QuicklyAddedProduct with Product.id as primary key

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                cjohn001 Christoph John
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: