DELETE A RECORD AND WRITE TO TABLE

[Lambro]

I have two tables, tblScannerTransactions and tbl ScanTranSummary. Table tblScannerTransactions stores all transactions as far as insert, delete and updates. When a record is written to tblScannerTransactions it updates table tblScanTranSummary. So basicly table tblScanTranSummary is the running sum of all my transactions. All this is great!

My problem: Say I delete a record by selecting the row by ID (auto int) field in the tblScanTranSummary table. When the row is deleted, there is no trace of where that record went. I want to have any record that is deleted from this table to be recorded in the tblScannerTransactions table. How would I go about doing this?

If this question is not clear, tell me what's not clear.

Thanks All

 

[cheyney]

You can set up a trigger for delete on tblScanTranSummary to insert into the tblScannerTransactions table.

Cheyney

 

[synapsevampire]

I'd use a trigger for this.

In the trigger you can reference:

select * from deleted to determine the row(s) involved in the delete.

There's also a INSTEAD OF DELETE trigger available, but I don't think that you need this.

From SQL Server help:

deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action. For example, to retrieve all values in the deleted table, use:
SELECT *
FROM deleted

In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text, ntext, or image column references in the inserted and deleted tables if the compatibility level is equal to 70. The text, ntext, and image values in the inserted and deleted tables cannot be accessed. To retrieve the new value in either an INSERT or UPDATE trigger, join the inserted table with the original update table. When the compatibility level is 65 or lower, null values are returned for inserted or deleted text, ntext, or image columns that allow null values; zero-length strings are returned if the columns are not nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views.

-k

 

[Transcend]

Are you writing the application that will be used to insert, update and delete? If you are then you can insert the data to the second table before you delete, if not use a trigger as synapse and cheney suggested.

Transcend

 

[Lambro]

This is my code to delete a record from tblScantranSummary:

<a href=&quot;delete.asp?ScanTranSummary_ID=<% = intScanTranSummary_ID %>&quot;><img src=&quot;delete_icon.gif&quot;></a>


This is the code on the delete.asp page:

SQL = &quot;DELETE FROM tblScanTranSummary WHERE tblScanTranSummary.ScanTranSummary_ID =&quot; & intScanTranSummary_ID & &quot;;&quot;

This all works fine.

I would like to convert this code into a stored procedure and use it. I'm not sure how to go about that.

My other question is, when the record is deleted, I would like it to record the transaction to a table so I have a history of what was deleted.

Thanks

 

[synapsevampire]

You should set up the deletes to be automatically copied to another file using a Trigger.

Stored Procedures are created on the database, and require special permission to do so, check with your DBA.

-k

 

[Lambro]

synapsevampire, could you give me an example?

Thanks

 

[tlbroadbent]

Please read the manual. The topic, CREATE TRIGGER in SQL BOL contains examples.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_create2_7eeq.asp

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm