Page 1 of 1
Hi, How to get select result(trigger) after I post Tuniquery
Posted: Sun 01 Aug 2010 00:46
by halenpolen
Hi, How to get select result(trigger) after I post with command->Tuniquery.post
I have a table with trigger after insert
CREATE TABLE Table_1
(
id int NOT NULL,
name varchar(20),
PRIMARY KEY (id)
)
GO
CREATE TRIGGER TABLE_1_insert
ON Table_1
AFTER INSERT AS
IF (SELECT id FROM INSERTED)>10
SELECT 1
ELSE
SELECT 0
GO
client side (delphi 5):
UniQuery1.SQL.Text:= 'SELECT * from table_1';
UniQuery1.Open;
UniQuery1.Append;
UniQuery1.FieldByName('id').Value:=15;
UniQuery1.FieldByName('name').Value:='Smith Brown';
UniQuery1.Post;
//Showmessage(inttostr(UniQuery1.result_POST_fields[0].value))//<-how to get command like this
The problem is :
How to get result select (select 1 or select 0) in UniQuery1 ??
Thx for ur help
Posted: Sun 01 Aug 2010 10:27
by DepSoft
I'm not sure whether you can do this with a TUniQuery the way you show.
What is the nature of what you're trying to do? There may be an alternative method.
Regards, Paul.
Posted: Mon 02 Aug 2010 09:07
by Dimon
A trigger is a database object that is executed automatically when a particular event occurs for a table. A client side should not and can not control work of triggers. The point is that a server doesn't return this information to the cilent. Therefore you can't get this value.
Posted: Fri 06 Aug 2010 03:40
by halenpolen
DepSoft wrote:I'm not sure whether you can do this with a TUniQuery the way you show.
What is the nature of what you're trying to do? There may be an alternative method.
Regards, Paul.
Hi,Thx Paul. You're right, TUniQuery.POST cann't do this. I need to refresh Tuniquery, indicator refresh is from value trigger(select 1 or select 0).
Dimon wrote: The point is that a server doesn't return this information to the cilent. Therefore you can't get this value.
Hi,Thx Dimon,I'm using msql server 2008. I disaggree with urs. The server still return information.
Example :
UniQuery2.SQL.Text:='insert into table_1(id,name)Values(''11'',''Smith Brown'')';
UniQuery2.Open;
ShowMessage(UniQuery2.fields[0].AsString);
//it return 1
, But I want to get value when TUniQuery.POST, I know TUniQuery.POST is like Execute, not give back result query. Is there another way Tuniquery.post to give back select result??
Posted: Fri 06 Aug 2010 10:10
by Dimon
To solve the problem you should use an additional TUniQuery in a SQL for record inserting. Link it to base TUniQuery via the TUniUpdateSQL component. And then you should process the AfterUpdateExecute event handler to get the trigger result set.
Code: Select all
procedure TForm1.OpenClick(Sender: TObject);
var
InsQuery: TUniQuery;
UniUpdateSQL: TUniUpdateSQL;
begin
InsQuery := TUniQuery.Create(self);
InsQuery.Connection := UniConnection1;
InsQuery.SQL.Text := 'INSERT INTO table_1(id, name) VALUES(:id, :name)';
UniUpdateSQL := TUniUpdateSQL.Create(self);
UniUpdateSQL.InsertObject := InsQuery;
UniQuery1.UpdateObject := UniUpdateSQL;
UniQuery1.SQL.Text:= 'SELECT * from table_1';
UniQuery1.Open;
UniQuery1.Append;
UniQuery1.FieldByName('id').Value := 16;
UniQuery1.FieldByName('name').Value := 'Smith Brown';
UniQuery1.Post;
end;
procedure TForm1.UniQuery1AfterUpdateExecute(Sender: TDataSet;
StatementTypes: TStatementTypes; Params: TDAParams);
begin
ShowMessage(InsQuery.Fields[0].AsString);
end;
Posted: Fri 06 Aug 2010 12:18
by halenpolen
Dimon wrote:To solve the problem you should use an additional TUniQuery in a SQL for record inserting. Link it to base TUniQuery via the TUniUpdateSQL component. And then you should process the AfterUpdateExecute event handler to get the trigger result set.
Thx Dimon, It's work. It's what I need.
