Master/Detail Updation: Reflecting Identity Value

Master-Detail is quite easy to explain, but its complexity lies when you’re implementing it particularly when the identity columns rely on database given values. Researching on this topic for several months, days and hours, finally things lightened up for me.

Instead having to rely on an auto increment value, I have to rely on a value set provided via trigger. Have to admit myself that the resource below was very helpful indeed to the quest of solving this problem:

http://www.eggheadcafe.com/forumarchives/NETFrameworkADONET/Nov2005/post24232248.asp
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/4d10fe87-2de8-42a8-8ef9-b9d46c0fd28d

The environment I was working with was Windows XP Pro SP+3, Visual Studio 2005, C#.net, Oracle XE DB, Oracle 10g client, System.Data.OracleCleint and TableAdapters;

To illustrate how I went on, I extracted the following tables from my project:

Journal(journalid*, xsactdate, xsactdesc);
JournalAccount(Journalid*, accountid*, xsactvalue);
PKEY(journal_PKEY);

I had the following triggers written for the Journal & JournalAccount table respectively.

Trigger for Journal Table as:

DECLARE
   nJournal_PKey PKey.Journal_PKey%TYPE;
   nChkJournalID Journal.JournalID%TYPE;
BEGIN
   -- this is to prevent posted Journals from being
   -- updated

   IF ( INSERTING ) THEN
      nChkJournalID := :new.JournalID;
   ELSE
      nChkJournalID := :old.JournalID;
   END IF;

   IF ( INSERTING AND ( :new.JournalID IS NULL ) ) THEN

      SELECT Journal_PKey INTO nJournal_PKey FROM PKey FOR UPDATE NOWAIT;

      UPDATE PKey SET Journal_PKey = Journal_PKey + 1;

      :new.JournalID   := nJournal_PKey + 1;
   END IF;
END;

Trigger for JournalAccount Table:

DECLARE
   nJournal_PKey PKey.Journal_PKey%TYPE;
   nChkJournalID Journal.JournalID%TYPE;
BEGIN
   -- this is to prevent posted Journals from being
   -- updated

   IF ( INSERTING ) THEN
      nChkJournalID := :new.JournalID;
   ELSE
      nChkJournalID := :old.JournalID;
   END IF;

   IF ( INSERTING AND ( :new.JournalID IS NULL ) ) THEN

      SELECT Journal_PKey INTO nJournal_PKey FROM PKey FOR UPDATE NOWAIT;

      :new.JournalID   := nJournal_PKey;
   END IF;
END;

Having followed what David Sceppa and Jason Kresowaty alias BinaryCoder wrote in their postings, things were going ok for me, except for the fact that Identities aren’t reflected upon saving to the table. Since the TableAdapters created without the InsertCommand, I have to include it by myself.

There were certain things which I need to modify, particularly Insert Query. Look into my Insert Query and you may see the Oracle Keyword “RETURNING”. The keyword is basically enables us to use it as an output parameter. I have inserted the temporary identity and getting back the value assigned via the trigger as an output parameter.

The following describes the “RETURNING” clause of Oracle: http://www.myoracleguide.com/s/Returning.htm

Finally have to amend the

System.Data.DataRowVersion

to

System.Data.DataRowVersion.Proposed

and the identity field (JournalID in my case)

System.Data.ParameterDirection

to

System.Data.ParameterDirection.InputOutput

The following is the InsertCommand in the TableAdapter for the Journal Table:

this._adapter.InsertCommand = new System.Data.OracleClient.OracleCommand(); this._adapter.InsertCommand.Connection = this.Connection;
this._adapter.InsertCommand.CommandText = "INSERT INTO JOURNALACCOUNT(JOURNALID, ACCOUNTID, XSACTVALUE, DISCOUNTEDPAYMENT) VALUES (:JOURNALID, :ACCOUNTID, :XSACTVALUE,'N') RETURNING JOURNALID INTO :JOURNALID";
this._adapter.InsertCommand.CommandType = System.Data.CommandType.Text;
this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("JOURNALID", System.Data.OracleClient.OracleType.Number, 22, System.Data.ParameterDirection.InputOutput, "JOURNALID", System.Data.DataRowVersion.Proposed, false, null));
this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("ACCOUNTID", System.Data.OracleClient.OracleType.Char, 15, System.Data.ParameterDirection.Input, "ACCOUNTID", System.Data.DataRowVersion.Current, false, null));
this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("XSACTVALUE", System.Data.OracleClient.OracleType.Number, 22, System.Data.ParameterDirection.Input, "XSACTVALUE", System.Data.DataRowVersion.Current, false, null));

The following is the insertcommand in the tableadapter for the JournalAccount table:

this._adapter.InsertCommand = new System.Data.OracleClient.OracleCommand();
this._adapter.InsertCommand.Connection = this.Connection;
this._adapter.InsertCommand.CommandText = "INSERT INTO JKSBSCHEMA.JOURNAL(JOURNALID, XSACTDATE, XSACTDESC) VALUES (:JOURNALID, :XSACTDATE, :XSACTDESC) RETURNING JOURNALID INTO :JOURNALID";
this._adapter.InsertCommand.CommandType = System.Data.CommandType.Text;
this._adapter.InsertCommand.UpdatedRowSource = System.Data.UpdateRowSource.Both;
this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("JOURNALID", System.Data.OracleClient.OracleType.Number, 22, System.Data.ParameterDirection.InputOutput, "JOURNALID", System.Data.DataRowVersion.Proposed, false, null));
this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("XSACTDATE", System.Data.OracleClient.OracleType.DateTime, 7, System.Data.ParameterDirection.Input, "XSACTDATE", System.Data.DataRowVersion.Current, false, null));
this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("XSACTDESC", System.Data.OracleClient.OracleType.VarChar, 40, System.Data.ParameterDirection.Input, "XSACTDESC", System.Data.DataRowVersion.Current, false, null));

Refer My Post @ MSDN Forums: http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/fbfdc09b-41bf-4290-9dea-6a9a85d5becf