Avatar of cula99
cula99

asked on 

Master detail same table

Hello,

my problem is… I don’t know, somehow stupid.
Datebase is InterBase.
Cached updates are on.
Supose, I have one table where storing organizations. In the same table, there are suborganisations. When I try to insert organisation it is not problem, ApplyUpdates Commit and done, database generate PrimaryKey. But I wont something else. I wont to put organizsation and suborganisations in the same time.

What I tried:
-      2 querys on same table and procedure in database that generate PK.
-      2 querys and 2 tables in the other there are trigger after insert that insert into orginal table and delete from it unnecesary data

Structure is like this

pk integer,
pk_sub integer,
name varchar(50);

if  pk=pk_sub it means that it is main organisation. In other chases pk_sub must exist in column pk



  OK:=True;
  if (SpisakQuery.State in [dsInsert]) then
    begin
      nrProc.ParamByName('PARAM').AsString:='ORG_PK';
      nrProc.ExecProc;
      Broj:=nrProc.ParamByName('NR’).AsInteger;
    end
  else
    BNr:=SpisakQueryORG_PK.AsInteger;
  try
    if (BQuery.UpdateStatus in [usModified, usInserted]) then
      begin
        if (AQuery.State in [dsInsert]) then
          begin
            AQueryORGANIZACIJA_PK.AsInteger:=Broj;
            AQuery.Post;
            BQuery.First;
            while not BQuery.Eof do
              begin
                BQuery.Edit;
                BQueryPK_sub.AsInteger:=BNr;
                BQuery.Post;
                BQuery.Next;
              end;
           end;
        AQuery.Transaction.Databases[0].ApplyUpdates([AQuery, BQuery]);
      end
    else
      begin
        AQueryORG_PK.AsInteger:=Broj;
        AQuery.Transaction.Databases[0].ApplyUpdates([AQuery]);
      end;
  except
    on EIBInterBaseError do
      begin
        ShowMessage('Something is bad');
        OK:=False;
      end;
  end;

  if OK then
    begin
      AQuery.Transaction.Commit;//Retaining;
      //temp:=AQueryORG_PK.AsString;
      AQuery.Close;
      AActionExecute(Sender);
      AQuery.Locate('ORG_PK', Broj, []);
      {Some action}
    end;
Delphi

Avatar of undefined
Last Comment
erkinbaba
Avatar of Wim ten Brink
Wim ten Brink
Flag of Netherlands image

Two queries on the same table should work fine but you have to remember that when inserting a master-detail combination, you will have to insert and POST the master record first, before you can add any detail records.

The simplest solution is by using a transaction to roll back any changes. You start by creating the transaction, insert the master record with just the minimum required values and post it. Then put the record in editmode while you fill in other master data and add the detail records. Once all detail records are added you can commit the transaction or do a rollback in case of an error.

The master record MUST exist before you can add detail records.
Avatar of cula99
cula99

ASKER

I did it and it works but what if 2 users try to insert data. If I POST master record I cant see PK. How can I solve this problem. In my example I did just like that but problem stands. I even make one "litle" example:


CREATE TABLE "TEST"
(
  "PK"      INTEGER NOT NULL,
  "PK_SUB"      INTEGER NOT NULL,
  "NAME"      VARCHAR(50),
CONSTRAINT "TEST_PK" PRIMARY KEY ("PK")
);
SET TERM ^ ;


/* Triggers only will work for SQL triggers */

CREATE TRIGGER "TEST_GEN" FOR "TEST"
ACTIVE BEFORE INSERT POSITION 0
AS
 DECLARE VARIABLE broj INTEGER;
BEGIN
 IF (NEW."PK" IS NULL) THEN
  BEGIN
   SELECT VREDNOST FROM FIND_KEY('TEST_PK')
   INTO :broj;
   NEW."PK" = :broj;
  END
 IF (NEW.PK_SUB IS NULL) THEN
  NEW.PK_SUB=NEW.PK;
END
 ^

COMMIT WORK ^
SET TERM ;^

CREATE TABLE "RBR"
(
  "IMEI"      VARCHAR(31) NOT NULL,
  "RB"      INTEGER NOT NULL,
CONSTRAINT "RBR_PK" PRIMARY KEY ("IMEI")
);
INSERT INTO "RBR" ("IMEI", "RB") VALUES ('TEST_PK', 25);

CREATE PROCEDURE "FIND_KEY" ( "PARAM" VARCHAR(30))
RETURNS ( "NUMB" INTEGER)
AS
BEGIN
 SELECT RB FROM RBR
 WHERE IME=:PARAM
 INTO :NUMB;
 UPDATE RBR SET RB=RB+1
 WHERE IMEI=:PARAM;
 SUSPEND;
END ^
SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;


{PAS}
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, IBCustomDataSet, IBUpdateSQL, IBDatabase, DB, IBQuery, StdCtrls,
  Mask, DBCtrls, Grids, DBGrids, ExtCtrls, ComCtrls, IBStoredProc;

type
  TForm1 = class(TForm)
    IBDatabase1: TIBDatabase;
    IBQuery1: TIBQuery;
    IBQuery2: TIBQuery;
    IBTransaction1: TIBTransaction;
    IBUpdateSQL1: TIBUpdateSQL;
    IBUpdateSQL2: TIBUpdateSQL;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    Button1: TButton;
    Panel1: TPanel;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    IBQuery1PK: TIntegerField;
    IBQuery1PK_SUB: TIntegerField;
    IBQuery1NAME: TIBStringField;
    Label1: TLabel;
    DBEdit1: TDBEdit;
    Label2: TLabel;
    DBEdit2: TDBEdit;
    Label3: TLabel;
    DBEdit3: TDBEdit;
    Button2: TButton;
    Panel2: TPanel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    DBEdit4: TDBEdit;
    DBEdit5: TDBEdit;
    DBEdit6: TDBEdit;
    DBGrid2: TDBGrid;
    Button3: TButton;
    Button4: TButton;
    Button5: TButton;
    IBQuery2PK: TIntegerField;
    IBQuery2PK_SUB: TIntegerField;
    IBQuery2NAME: TIBStringField;
    IBStoredProc1: TIBStoredProc;
    Button6: TButton;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Button6Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  DBGrid1.DataSource:=nil;
  if not IBQuery1.Active then
    IBQuery1.Open;
  IBQuery1.Append;
end;

procedure TForm1.Button2Click(Sender: TObject);
var
  i:integer;
begin
  IBStoredProc1.ParamByName('PARAM').AsString:='TEST_PK';
  IBStoredProc1.ExecProc;
  i:=IBStoredProc1.ParamByName('NUMB').AsInteger;
  IBQuery1PK.AsInteger:=i;
  IBQuery1PK_SUB.AsInteger:=i;
  IBQuery1.ApplyUpdates;

  if not IBQuery2.Active then
    IBQuery2.Open;
  IBQuery2.Append;
  IBQuery2PK_SUB.AsInteger:=i;
  IBQuery2PK_SUB.AsInteger:=IBQuery1PK.AsInteger;
  PageControl1.ActivePage:=TabSheet2;
end;

procedure TForm1.Button4Click(Sender: TObject);
begin
  IBQuery2.ApplyUpdates;
  PageControl1.ActivePage:=TabSheet1;
end;

procedure TForm1.Button3Click(Sender: TObject);
begin
  IBQuery2.ApplyUpdates;
  IBQuery2.Append;
end;

procedure TForm1.Button5Click(Sender: TObject);
begin
  IBTransaction1.CommitRetaining;
  DBGrid1.DataSource:=DataSource1;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  if IBDatabase1.Connected then
    IBDatabase1.Close;
  IBDatabase1.Open;
end;

procedure TForm1.Button6Click(Sender: TObject);
begin
  IBQuery1.Close;
  IBQuery1.Open;
end;

end.

{DFM}

object Form1: TForm1
  Left = 192
  Top = 114
  Width = 870
  Height = 500
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object PageControl1: TPageControl
    Left = 280
    Top = 0
    Width = 582
    Height = 466
    ActivePage = TabSheet1
    Align = alRight
    TabOrder = 0
    object TabSheet1: TTabSheet
      Caption = 'TabSheet1'
      object Panel1: TPanel
        Left = 0
        Top = 0
        Width = 574
        Height = 145
        Align = alTop
        TabOrder = 0
        object Label1: TLabel
          Left = 8
          Top = 8
          Width = 14
          Height = 13
          Caption = 'PK'
          FocusControl = DBEdit1
        end
        object Label2: TLabel
          Left = 8
          Top = 48
          Width = 42
          Height = 13
          Caption = 'PK_SUB'
          FocusControl = DBEdit2
        end
        object Label3: TLabel
          Left = 8
          Top = 88
          Width = 31
          Height = 13
          Caption = 'NAME'
          FocusControl = DBEdit3
        end
        object DBEdit1: TDBEdit
          Left = 8
          Top = 24
          Width = 134
          Height = 21
          DataField = 'PK'
          DataSource = DataSource1
          TabOrder = 0
        end
        object DBEdit2: TDBEdit
          Left = 8
          Top = 64
          Width = 134
          Height = 21
          DataField = 'PK_SUB'
          DataSource = DataSource1
          TabOrder = 1
        end
        object DBEdit3: TDBEdit
          Left = 8
          Top = 104
          Width = 414
          Height = 21
          DataField = 'NAME'
          DataSource = DataSource1
          TabOrder = 2
        end
        object Button2: TButton
          Left = 456
          Top = 72
          Width = 75
          Height = 25
          Caption = 'Add subb'
          TabOrder = 3
          OnClick = Button2Click
        end
        object Button5: TButton
          Left = 456
          Top = 104
          Width = 75
          Height = 25
          Caption = 'OK'
          TabOrder = 4
          OnClick = Button5Click
        end
      end
      object DBGrid1: TDBGrid
        Left = 0
        Top = 145
        Width = 574
        Height = 293
        Align = alClient
        DataSource = DataSource1
        TabOrder = 1
        TitleFont.Charset = DEFAULT_CHARSET
        TitleFont.Color = clWindowText
        TitleFont.Height = -11
        TitleFont.Name = 'MS Sans Serif'
        TitleFont.Style = []
      end
    end
    object TabSheet2: TTabSheet
      Caption = 'TabSheet2'
      ImageIndex = 1
      object Panel2: TPanel
        Left = 0
        Top = 0
        Width = 574
        Height = 145
        Align = alTop
        TabOrder = 0
        object Label4: TLabel
          Left = 8
          Top = 8
          Width = 14
          Height = 13
          Caption = 'PK'
          FocusControl = DBEdit4
        end
        object Label5: TLabel
          Left = 8
          Top = 48
          Width = 42
          Height = 13
          Caption = 'PK_SUB'
          FocusControl = DBEdit5
        end
        object Label6: TLabel
          Left = 8
          Top = 88
          Width = 31
          Height = 13
          Caption = 'NAME'
          FocusControl = DBEdit6
        end
        object DBEdit4: TDBEdit
          Left = 8
          Top = 24
          Width = 134
          Height = 21
          DataField = 'PK'
          DataSource = DataSource2
          TabOrder = 0
        end
        object DBEdit5: TDBEdit
          Left = 8
          Top = 64
          Width = 134
          Height = 21
          DataField = 'PK_SUB'
          DataSource = DataSource2
          TabOrder = 1
        end
        object DBEdit6: TDBEdit
          Left = 8
          Top = 104
          Width = 414
          Height = 21
          DataField = 'NAME'
          DataSource = DataSource2
          TabOrder = 2
        end
        object Button3: TButton
          Left = 440
          Top = 64
          Width = 75
          Height = 25
          Caption = 'Add subb'
          TabOrder = 3
          OnClick = Button3Click
        end
        object Button4: TButton
          Left = 440
          Top = 96
          Width = 75
          Height = 25
          Caption = 'OK'
          TabOrder = 4
          OnClick = Button4Click
        end
      end
      object DBGrid2: TDBGrid
        Left = 0
        Top = 145
        Width = 574
        Height = 293
        Align = alClient
        TabOrder = 1
        TitleFont.Charset = DEFAULT_CHARSET
        TitleFont.Color = clWindowText
        TitleFont.Height = -11
        TitleFont.Name = 'MS Sans Serif'
        TitleFont.Style = []
      end
    end
  end
  object Button1: TButton
    Left = 200
    Top = 16
    Width = 75
    Height = 25
    Caption = 'Add'
    TabOrder = 1
    OnClick = Button1Click
  end
  object Button6: TButton
    Left = 192
    Top = 208
    Width = 75
    Height = 25
    Caption = 'Query'
    TabOrder = 2
    OnClick = Button6Click
  end
  object IBDatabase1: TIBDatabase
    Connected = True
    DatabaseName = 'C:\InterBase\SrcDelphi7\Baza\BAZA2007.IB'
    Params.Strings = (
      'user_name=CULA'
      'password=fatal')
    LoginPrompt = False
    DefaultTransaction = IBTransaction1
    IdleTimer = 0
    SQLDialect = 3
    TraceFlags = []
    Left = 32
    Top = 16
  end
  object IBQuery1: TIBQuery
    Database = IBDatabase1
    Transaction = IBTransaction1
    BufferChunks = 1000
    CachedUpdates = True
    SQL.Strings = (
      'select * from TEST')
    UpdateObject = IBUpdateSQL1
    Left = 32
    Top = 64
    object IBQuery1PK: TIntegerField
      FieldName = 'PK'
      Origin = 'TEST.PK'
      Required = True
    end
    object IBQuery1PK_SUB: TIntegerField
      FieldName = 'PK_SUB'
      Origin = 'TEST.PK_SUB'
      Required = True
    end
    object IBQuery1NAME: TIBStringField
      FieldName = 'NAME'
      Origin = 'TEST.NAME'
      Size = 50
    end
  end
  object IBQuery2: TIBQuery
    Database = IBDatabase1
    Transaction = IBTransaction1
    BufferChunks = 1000
    CachedUpdates = True
    SQL.Strings = (
      'select * from TEST')
    UpdateObject = IBUpdateSQL2
    Left = 32
    Top = 112
    object IBQuery2PK: TIntegerField
      FieldName = 'PK'
      Origin = 'TEST.PK'
    end
    object IBQuery2PK_SUB: TIntegerField
      FieldName = 'PK_SUB'
      Origin = 'TEST.PK_SUB'
      Required = True
    end
    object IBQuery2NAME: TIBStringField
      FieldName = 'NAME'
      Origin = 'TEST.NAME'
      Size = 50
    end
  end
  object IBTransaction1: TIBTransaction
    Active = False
    DefaultDatabase = IBDatabase1
    Params.Strings = (
      'concurrency')
    AutoStopAction = saNone
    Left = 96
    Top = 16
  end
  object IBUpdateSQL1: TIBUpdateSQL
    RefreshSQL.Strings = (
      'Select '
      '  PK,'
      '  PK_SUB,'
      '  NAME'
      'from TEST '
      'where'
      '  PK = :PK')
    ModifySQL.Strings = (
      'update TEST'
      'set'
      '  PK = :PK,'
      '  PK_SUB = :PK_SUB,'
      '  NAME = :NAME'
      'where'
      '  PK = :OLD_PK')
    InsertSQL.Strings = (
      'insert into TEST'
      '  (PK, PK_SUB, NAME)'
      'values'
      '  (:PK, :PK_SUB, :NAME)')
    DeleteSQL.Strings = (
      'delete from TEST'
      'where'
      '  PK = :OLD_PK')
    Left = 96
    Top = 64
  end
  object IBUpdateSQL2: TIBUpdateSQL
    RefreshSQL.Strings = (
      'Select '
      '  PK,'
      '  PK_SUB,'
      '  NAME'
      'from TEST '
      'where'
      '  PK = :PK')
    ModifySQL.Strings = (
      'update TEST'
      'set'
      '  PK = :PK,'
      '  PK_SUB = :PK_SUB,'
      '  NAME = :NAME'
      'where'
      '  PK = :OLD_PK')
    InsertSQL.Strings = (
      'insert into TEST'
      '  (PK, PK_SUB, NAME)'
      'values'
      '  (:PK, :PK_SUB, :NAME)')
    DeleteSQL.Strings = (
      'delete from TEST'
      'where'
      '  PK = :OLD_PK')
    Left = 96
    Top = 112
  end
  object DataSource1: TDataSource
    DataSet = IBQuery1
    Left = 168
    Top = 64
  end
  object DataSource2: TDataSource
    DataSet = IBQuery2
    Left = 168
    Top = 112
  end
  object IBStoredProc1: TIBStoredProc
    Database = IBDatabase1
    Transaction = IBTransaction1
    StoredProcName = 'FIND_KEY'
    Left = 32
    Top = 176
    ParamData = <
      item
        DataType = ftInteger
        Name = 'NUMB'
        ParamType = ptOutput
        Value = 0
      end
      item
        DataType = ftString
        Name = 'PARAM'
        ParamType = ptInput
        Value = ''
      end>
  end
end

Avatar of cula99
cula99

ASKER

correction

SET TERM ^ ;


/* Triggers only will work for SQL triggers */

CREATE TRIGGER "TEST_GEN" FOR "TEST"
ACTIVE BEFORE INSERT POSITION 0
AS
 DECLARE VARIABLE broj INTEGER;
BEGIN
 IF (NEW."PK" IS NULL) THEN
  BEGIN
   SELECT NUMB FROM FIND_KEY('TEST_PK')
   INTO :broj;
   NEW."PK" = :broj;
  END
 IF (NEW.PK_SUB IS NULL) THEN
  NEW.PK_SUB=NEW.PK;
END
 ^

COMMIT WORK ^
SET TERM ;^
ASKER CERTIFIED SOLUTION
Avatar of erkinbaba
erkinbaba
Flag of Türkiye image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Delphi
Delphi

Delphi is the most powerful Object Pascal IDE and component library for cross-platform Native App Development with flexible Cloud services and broad IoT connectivity. It provides powerful VCL controls for Windows 10 and enables FMX development for Windows, Mac and Mobile. Delphi is your choice for ultrafast Enterprise Strong Development™. Look for increased memory for large projects, extended multi-monitor support, improved Object Inspector and much more. Delphi is 5x faster for development and deployment across multiple desktop, mobile, cloud and database platforms including 32-bit and 64-bit Windows 10.

60K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo