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'
nrProc.ExecProc;
Broj:=nrProc.ParamByName('
end
else
BNr:=SpisakQueryORG_PK.AsI
try
if (BQuery.UpdateStatus in [usModified, usInserted]) then
begin
if (AQuery.State in [dsInsert]) then
begin
AQueryORGANIZACIJA_PK.AsIn
AQuery.Post;
BQuery.First;
while not BQuery.Eof do
begin
BQuery.Edit;
BQueryPK_sub.AsInteger:=BN
BQuery.Post;
BQuery.Next;
end;
end;
AQuery.Transaction.Databas
end
else
begin
AQueryORG_PK.AsInteger:=Br
AQuery.Transaction.Databas
end;
except
on EIBInterBaseError do
begin
ShowMessage('Something is bad');
OK:=False;
end;
end;
if OK then
begin
AQuery.Transaction.Commit;
//temp:=AQueryORG_PK.AsStr
AQuery.Close;
AActionExecute(Sender);
AQuery.Locate('ORG_PK', Broj, []);
{Some action}
end;
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'
nrProc.ExecProc;
Broj:=nrProc.ParamByName('
end
else
BNr:=SpisakQueryORG_PK.AsI
try
if (BQuery.UpdateStatus in [usModified, usInserted]) then
begin
if (AQuery.State in [dsInsert]) then
begin
AQueryORGANIZACIJA_PK.AsIn
AQuery.Post;
BQuery.First;
while not BQuery.Eof do
begin
BQuery.Edit;
BQueryPK_sub.AsInteger:=BN
BQuery.Post;
BQuery.Next;
end;
end;
AQuery.Transaction.Databas
end
else
begin
AQueryORG_PK.AsInteger:=Br
AQuery.Transaction.Databas
end;
except
on EIBInterBaseError do
begin
ShowMessage('Something is bad');
OK:=False;
end;
end;
if OK then
begin
AQuery.Transaction.Commit;
//temp:=AQueryORG_PK.AsStr
AQuery.Close;
AActionExecute(Sender);
AQuery.Locate('ORG_PK', Broj, []);
{Some action}
end;
Last Comment
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
begin
DBGrid1.DataSource:=nil;
if not IBQuery1.Active then
IBQuery1.Open;
IBQuery1.Append;
end;
procedure TForm1.Button2Click(Sender
var
i:integer;
begin
IBStoredProc1.ParamByName(
IBStoredProc1.ExecProc;
i:=IBStoredProc1.ParamByNa
IBQuery1PK.AsInteger:=i;
IBQuery1PK_SUB.AsInteger:=
IBQuery1.ApplyUpdates;
if not IBQuery2.Active then
IBQuery2.Open;
IBQuery2.Append;
IBQuery2PK_SUB.AsInteger:=
IBQuery2PK_SUB.AsInteger:=
PageControl1.ActivePage:=T
end;
procedure TForm1.Button4Click(Sender
begin
IBQuery2.ApplyUpdates;
PageControl1.ActivePage:=T
end;
procedure TForm1.Button3Click(Sender
begin
IBQuery2.ApplyUpdates;
IBQuery2.Append;
end;
procedure TForm1.Button5Click(Sender
begin
IBTransaction1.CommitRetai
DBGrid1.DataSource:=DataSo
end;
procedure TForm1.FormCreate(Sender: TObject);
begin
if IBDatabase1.Connected then
IBDatabase1.Close;
IBDatabase1.Open;
end;
procedure TForm1.Button6Click(Sender
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\B
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
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
begin
DBGrid1.DataSource:=nil;
if not IBQuery1.Active then
IBQuery1.Open;
IBQuery1.Append;
end;
procedure TForm1.Button2Click(Sender
var
i:integer;
begin
IBStoredProc1.ParamByName(
IBStoredProc1.ExecProc;
i:=IBStoredProc1.ParamByNa
IBQuery1PK.AsInteger:=i;
IBQuery1PK_SUB.AsInteger:=
IBQuery1.ApplyUpdates;
if not IBQuery2.Active then
IBQuery2.Open;
IBQuery2.Append;
IBQuery2PK_SUB.AsInteger:=
IBQuery2PK_SUB.AsInteger:=
PageControl1.ActivePage:=T
end;
procedure TForm1.Button4Click(Sender
begin
IBQuery2.ApplyUpdates;
PageControl1.ActivePage:=T
end;
procedure TForm1.Button3Click(Sender
begin
IBQuery2.ApplyUpdates;
IBQuery2.Append;
end;
procedure TForm1.Button5Click(Sender
begin
IBTransaction1.CommitRetai
DBGrid1.DataSource:=DataSo
end;
procedure TForm1.FormCreate(Sender: TObject);
begin
if IBDatabase1.Connected then
IBDatabase1.Close;
IBDatabase1.Open;
end;
procedure TForm1.Button6Click(Sender
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\B
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
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 ;^
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
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.
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
TRUSTED BY
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.