TADOQuery联接表-插入\从结果中删除记录 [英] TADOQuery Join tables - Insert \ Delete records from result

问题描述

使用单个 TADOQuery 我使用左外部联接从两个不同的表中提取记录：

Using a single TADOQuery i pull records from two different tables using a left outer join:

Select M*, D.* from Courier M Left outer join Courier_VT D on M.Courier_Identifier = D.FK_Courier_Identifier

我使用 TDBGrid 成功将字段更新发布到我的MSSQL DB。

I use a TDBGrid to successfully post field updates to my MSSQL DB.

由于存在外键引用（ FK_Courier_Identifier 和 Courier_Identifier ），所以在插入记录时出现错误

Since there is foreign key reference (FK_Courier_Identifier with Courier_Identifier) I get a error when I insert a record,

不能在表Courier_VT的'FK_Courier_Identifier'列中插入Null值；列不允许为空

Cannot Insert the value Null in to column 'FK_Courier_Identifier', table Courier_VT; column does not allow null

但是在Courier表中发布了一条记录，我确实知道我需要分配 Courier_Identifier 到 FK_Courier_Identifier ，但不要在何处以及如何做

but a record is posted in Courier table, i do know that i need to assign the Courier_Identifier to FK_Courier_Identifier before posting but don't how and where to do it

在这种情况下，我们如何插入\删除记录？是否可以使用单个 TADOQuery 来实现？

How do we Insert \ Delete records in this scenario ? Is it possible to achieve using a single TADOQuery ?

推荐答案

当连接多个表时，AFAIK TADOQuery无法处理插入/删除/更新语句。其背后的原因是它不知道必须更新哪个表或如何执行。

AFAIK TADOQuery is unable to handle insert/delete/update statements when multiple tables are joined. The reason behind it is that it can not know which table it has to update or how to do it.

其他数据库访问组件的常用方法是提供一个每个DML句子类型的属性（ ODAC组件是一个示例），或者您必须添加第二个链接到查询的更新SQL组件将包含DML语句（ Zeos 是一个示例

The usual approach with other database access components is to either provide a property for each type of DML sentence (ODAC components are one example) or you have to add a second "update SQL" component linked to your query which will contain the DML sentences (Zeos is one example of components that use this approach).

请注意，最好的选择是使用BeforeDelete和BeforePost事件处理程序来处理您的情况。基本上，您将使用它们来发出DML语句，并使用某些storedproc或sql组件执行它，然后中止事件处理程序。检查对此的可接受答案这样的问题以获取更多信息和代码示例。

Said this, probably your best bet is to use the BeforeDelete and BeforePost event handlers to treat your scenario. Basically you would use them to issue the DML sentence, execute it with some storedproc or sql component and then abort the event handler. Check the accepted answer to this SO question for more information and a code sample.

编辑：如果您的代码可以按照评论中的说明处理更新和删除操作，那么问题就出在插入时分配了 FK_Courier_Identifier （应该更仔细地阅读问题。） ..），您可以使用OnBeforePost事件处理程序解决：

EDIT: if your code can handle the updates and deletes as you say in your comment, then the problem only lies with the assignment of the FK_Courier_Identifier on inserting (should have read the question more carefully...), which you can solve by using the OnBeforePost event handler:

procedure TMyForm.MyADOQueryBeforePost(Sender: TObject);
begin
  MyADOQuery.FieldByName('FK_Courier_Identifier').AsString := CourierId;
end;

当然，您将需要修改此代码，因为在这里我假设字段是 varchar ，并且您已经知道在数据库中插入Courier ID的值之前。

Of course, you will need to adapt this code since here I am supposing the field is a varchar and that you know prior to inserting in the database the value of Courier ID.

HTH

这篇关于TADOQuery联接表-插入\从结果中删除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！