can i do inserting data into one table and at the same time i want to delete the same recordset in another table. it just like transferring data from active table to archieve table. i have problem to do that.can somebody give me the solution
you might be able to write a trigger on the table that you update... On an update to that table, you could add a delete SQL statement in the trigger to delete the record from the other table. Otherwise I would do it in code or a stored procedure..
What is customarily done is to first do the INSERT query, then do the DELETE query, and do both steps within the purview of a single transaction.
A "transaction" is an atomic unit of operation: either everything succeeds, or nothing does. Therefore, either all of the rows are successfully copied and deleted, or nothing at all happens. (The transaction is "rolled back.")
When large numbers of records are being copied, it usually becomes necessary to do the job in smaller chunks, each performed by a transaction. This creates a situation whereby, if the operation fails, one or more (complete) chunks will have been copied but zero or more chunks may remain unchanged. But it controls the size of the "rollback file" that would otherwise have to be built.
Originally posted by fisya
actually i'm not really understand what both of u talking about because i'm new in asp. so, i really need help to elaborate more
objRS.open "Main", objConn, 3, 3 'open recored set for addnew
objRS("Record") = request.form("txtRecordNum") 'set DB field = ?
objRS.Update 'adds recored
objRS.Close 'close record set
'Create a sql varibale
SQL = "Select * FROM DeleteRecord WHERE (Record = '" & what ever record you want to delete & "')
objRS.open SQL, objConn, 3, 3 'open Record set for deletion
if objRS.EOF then ' check to see if record exists first
Error msg record does not exist
objRS.delete 'delete current record
objRS.close ' close the recored set
set objRS = nothing ' set the record set object to nothing
objConn.close 'close the connection
Put this were ever yo u need it to fire. This is one way of doing it.