Hi, How to get select result(trigger) after I post Tuniquery

[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

[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.

[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.

[halenpolen]

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).

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??

[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;

[halenpolen]

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.