执行插入，然后登录一个SQL命令

I've been asked to implement some code that will update a row in a MS SQL Server database and then use a stored proc to insert the update in a history table. We can't add a stored proc to do this since we don't control the database. I know in stored procs you can do the update and then call execute on another stored proc. Can I set it up to do this in code using one SQL command?

我被要求实现一些代码,这些代码将更新MS SQL Server数据库中的行,然后使用存储过程将更新插入历史记录表中。我们无法添加存储过程来执行此操作,因为我们不控制数据库。我知道在存储过程中你可以进行更新,然后在另一个存储过程中调用execute。我可以使用一个SQL命令将其设置为在代码中执行此操作吗?

6 个解决方案

#1

Either run them both in the same statement (separate the separate commands by a semi-colon) or a use a transaction so you can rollback the first statement if the 2nd fails.

要么在同一个语句中运行它们(用分号分隔单独的命令),要么使用事务处理,这样如果第二个语句失败就可以回滚第一个语句。

#2

You don't really need a stored proc for this. The question really boils down to whether or not you have control over all the inserts. If in fact you have access to all the inserts, you can simply wrap an insert into datatable, and a insert into historytable in a single transasction. This will ensure that both are completed for 'success' to occur. However, when accessing to tables in sequence within a transaction you need to make sure you don't lock historytable then datatable, or else you could have a deadlock situation.

你真的不需要存储过程。问题实际上归结为您是否可以控制所有插入。如果实际上您可以访问所有插入,则可以简单地将插入包装到数据表中,并在单个transasction中插入历史记录表。这将确保两者都完成“成功”。但是,在事务中按顺序访问表时,您需要确保不锁定历史记录然后数据表,否则可能会出现死锁情况。

However, if you do not have control over the inserts, you can add a trigger to certain db systems that will give you access to the data that are modified, inserted or deleted. It may or may not give you all the data you need, like who did the insert, update or delete, but it will tell you what changed.

但是,如果您无法控制插入,则可以向某些数据库系统添加触发器,以便访问已修改,插入或删除的数据。它可能会也可能不会为您提供所需的所有数据,例如谁进行了插入,更新或删除,但它会告诉您更改的内容。

#3

You can also create sql triggers.

您还可以创建sql触发器。

#4

Insufficient information -- what SQL server? Why have a history table?

信息不足 - 什么是SQL服务器?为什么有历史表?

Triggers will do this sort of thing. MySQL's binlog might be more useful for you.

触发器会做这种事情。 MySQL的binlog可能对你更有用。

You say you don't control the database. Do you control the code that accesses it? Add logging there and keep it out of the SQL server entirely.

你说你不控制数据库。你控制访问它的代码吗?在那里添加日志记录并将其完全保留在SQL服务器之外。

#5

Thanks all for your reply, below is a synopsis of what I ended up doing. Now to test to see if the trans actually roll back in event of a fail.

谢谢大家的回复,下面是我最终做的事情的概要。现在测试以查看trans是否在发生故障时实际回滚。

sSQL = "BEGIN TRANSACTION;" & _
           " Update table set col1 = @col1, col2 = @col2" & _
           " where col3 = @col3 and " & _
           " EXECUTE addcontacthistoryentry @parm1, @parm2, @parm3, @parm4, @parm5, @parm6; " & _
           "COMMIT TRANSACTION;"

#6

Depending on your library, you can usually just put both queries in one Command String, separated by a semi-colon.

根据您的库,您通常可以将两个查询放在一个命令字符串中,用分号分隔。

#1