同时插入2个表 [英] simultaneous insert into 2 tables

问题描述

我对编程很陌生，并且在使用SQLITE进行插入"时遇到问题.我有2张这样的桌子:

table1:
1:reg_no< number>(自动生成)(主键)
2:名称< text>

table2:
1:reg_no< number> (主键)(表1中的外键)
2:course_name< text>

我需要的是，每当"table1"中出现新插入时，"table2"中的条目也会出现.
我怎么知道"table1"中的自动生成的字段是什么?
我可以使用"select max()"来完成此操作.但是它增加了步骤..

谢谢

Debasish Sahu

Hi There,

I''m quite new to prog and facing a problem in "insert" using SQLITE. I have 2 tables like this:

table1:
1: reg_no <number>(auto generated)(primary key)
2:name <text>

table2:
1:reg_no <number> (primary key)(foreign key from table1)
2:course_name <text>

What I need is, whenever a new insert happens into "table1" an entry to "table2" also.
How can I know what is that auto-generated field in "table1"?
I can do this by using "select max()".. but it''s increasing the steps..

Thanx

Debasish Sahu

推荐答案

您在这里遇到许多问题:首先，最重要的一点是SQLite不支持处理此问题的最佳方法:使用存储过程. >
另一个是自动生成的索引是Stan(他是Satan li脚的兄弟)的工作，我正因为这个原因而避免使用它们-当您插入记录时，您不知道索引是什么.使用SQLITE并不是这样的问题-您可以使用MAX来检索它，但这很痛苦，也不是一个好习惯，因为如果您想移至真实"数据库(例如， SQL Server-在多用户环境中使用MAX来检索您的ID是非常危险的，因为它可以间歇地检索其他人的ID.

相反，我建议使用唯一标识符"字段而不是自动递增的整数:您在代码中生成了Guid ID，然后将记录插入表中，可能是在事务块中.由于您控制ID，因此永远不会出错.令人惊讶的是，几乎没有性能开销，如果以后再使用复制，它可以节省数据库空间，因为无论如何都需要GUID，并且在插入行时将由DB系统创建.

You have a number of problems here: first and foremost that SQLite does not support the best way to handle this: use a stored procedure.

The other is that auto generated indexes are the work of Stan (he is Satan''s limp-wristed brother) and I avoid using them for just this reason - you don''t know what the index is when you insert a record. With SQLITE it''s not such a problem - you could use MAX to retrieve it, but it''s a pain, and not a good practice as it does not scale well if you want to move to a "real" database such as SQL Server - in a multiuser environment using MAX to retrieve your ID is positively dangerous as it could retrieve someone else''s ID instead, intermittently.

Instead, I would suggest using a Unique Identifier field instead of an autoincremented integer: You generate the Guid ID in your code, and insert the record into the table, probably within a transaction block. Since you control the ID, there can never be a mistake. Surprisingly, there is little or no performance overhead, and if you start to use replication later it can save database space as a GUID is needed for that anyway, and would be created by the DB system when you insert the row.

这篇关于同时插入2个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！