execute an insert and then log in one sqlcommand

Question

I've been asked to implement some code that will update a row in a ms sql 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 sprocs 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 sqlcommand?

Answer 1

You can also create sql triggers.

Answer 2

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

Answer 3

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

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

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.

Answer 4

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.

Answer 5

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.

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.

Answer 6

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.

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