Saving deleted records to another table

Question

I am deleting records from one table (based on a condition) like :

procedure TForm3.AdvGlowButton1Click(Sender: TObject);
begin
 if MessageDlg('Are you sure???' , mtConfirmation, [mbYes, mbNo], 0) = mrNo then
    Abort else
Case cxRadioGroup1.ItemIndex of
0: begin
with Form1.ABSQuery1 do begin
Form1.ABSQuery1.Close;
Form1.ABSQuery1.SQL.Clear;
Form1.ABSQuery1.SQL.Text :='delete from LOG where status="YES" ';
Form1.ABSQuery1.ExecSQL;
Form1.ABSTable1.Refresh;
end;
end;
End;
end;

However,I want to save these deleted records in another table that I have created for the purpose (LOG_ARCHIVE) which is identical to the LOG table. So how do I save these deleted records over there ?

@Abelisto: According to the Absolute Database [documentation](http://www.componentace.com/search/?query_string=create+trigger&x=0&y=0), there's no trigger support (at least a search on `triggers` and `create trigger` both don't return anything about them). — Ken White, Mar 24 '13 at 04:34
@KenWhite: Sorry, it is because of my carelessness. Instead of the "ABSQuery" I read "ADOQuery" :| — Abelisto, Mar 24 '13 at 07:26

Ken White · Accepted Answer · 2013-03-24T15:38:42.247

3

If you were using a database that supported it, you could use a BEFORE DELETE trigger. However, according to a search on the Absolute Database documentation, there's no support for CREATE TRIGGER and a search on triggers at the same site returns nothing about them either.

The lack of trigger support probably just leaves you with performing an INSERT into the other table first, before doing the DELETE from your LOG table. According to the documentation again, a query is able to be used as the source of data for an INSERT (see the second example on the linked page). This means you can do something like this:

ABSQuery1.SQL.Text := 'insert into LOG_ARCHIVE'#13 +
                           '(select * from LOG where status = ''Yes'')';
ABSQuery1.SQL.ExecSQL;
ABSQuery1.Close;

{ 
  No need to use SQL.Clear here. Setting the SQL.Text replaces
  what was there before with new text.
}
ABSQuery1.SQL.Text :='delete from LOG where status=''YES''';
ABSQuery1.ExecSQL;

You really should wrap this entire operation in a transaction (Delphi example here), so that in case something fails both the INSERT and DELETE can be undone. (For instance, if the INSERT works putting the rows in the LOG_ARCHIVE file, but the DELETE then fails for some reason, you have no way to remove the rows you inserted into the archive file.) A transaction can be started before you do the INSERT, rolled back if it (or the DELETE fails or committed if both of them succeed.

edited Mar 24 '13 at 15:38

answered Mar 24 '13 at 02:07

Ken White

123,280
14
225
444

1

+1 possibly the downvoter did not agree with (the status = "YES" is invalid SQL syntax). With a poor design an status as varchar it might work. – bummi Mar 24 '13 at 06:59
@bummi: Thanks. :-) I'd remove it, but I don't think it's wrong; I can't think of any SQL DB that would allow that even as a VARCHAR with poor design. – Ken White Mar 24 '13 at 07:28
@KenWhite Try a simple test with MySQL (as one of any) ... using "" is valid – Sir Rufo Mar 24 '13 at 08:32
@SirRufo: OK. That's one exception (MySQL, which is a great example of not being compliant with standards). :-) I don't see any indication that ABS supports that in their documentation. I'll remove that mention from my question, though, since you did indeed show me a single DBMS that supported it. :-) In *real* DBs, "YES" would return a constant value for a `SELECT`, as in `SELECT "Y" AS MARRIED, "STUPID" AS Intelligence FROM MYTable". It isn't allowed as the condition of a where statement in SQL Server, for instance, or even the much smaller Advantage Database Server. MySQL is a poor example. – Ken White Mar 24 '13 at 14:30
A few comments: `ABSQuery -> ABSQuery1`; I also don't think `ABSQuery1.Close` is needed. a Transaction is must here IMHO (if the ABS DB supports it). – kobik Mar 24 '13 at 15:03
@kobik: The poster didn't ask about how to use a transaction, and there's none in the original question. The `Close` was included also because it was in the original code. The question here was how to keep a copy of rows before they were deleted, not a tutorial on properly writing database applications. I answered the question asked. :-) Thanks for the catch on the missing `1` - I missed it. – Ken White Mar 24 '13 at 15:06
1

You *did* remove the `SQL.Clear` that was originally in the question :) And I still think [Transactions](http://www.componentace.com/help/absdb_manual/transactions.htm) is worth mentioning for this particular task, because otherwise it may lead to data corruption if the `delete` statement fails. – kobik Mar 24 '13 at 15:27
The `SQL.Clear` in the original question was totally wasted. I don't know what the poster wants to do here, so I'll let them decide whether the `Close` is needed or not. I'll add a note about transactions, since they are supported by ADB, and add a link to the example on their site. – Ken White Mar 24 '13 at 15:33
I find it a good practice to always clear the SQL text of a query before using it especially if the query gets used more than once. Just in case ...Also I do not get what's wrong with (status = "YES") syntax ? – user763539 Mar 24 '13 at 17:48
I will see how to wrap this thing in a transaction like ken suggested... Thank you all .... – user763539 Mar 24 '13 at 17:50
`status = "YES"` is not standard SQL. If ADB allows it, it's fine. If you could someday change to a more standards-compliant DBMS like SQL Server, it will fail. From SQL Server Books OnLine `WHERE` example: `SELECT ProductID, Name FROM Production.Product WHERE Name = 'Blade'` - see the single quotes instead of double? – Ken White Mar 24 '13 at 17:55
Just checked to make sure, and according to the [ADB docs](http://www.componentace.com/help/absdb_manual/selectstatement.htm) their syntax is single-quotes too: `SELECT * FROM events WHERE Event_Name LIKE '%men%'` – Ken White Mar 24 '13 at 18:03
It's Delphi that wont let me use single quotes. If I use single quotes (status = 'YES') then it wont compile. Also I am a little perplexed about using transaction. First I archive the events and then delete them. If any of them fails I end up with orphans. If I archive and then deletion fails, I end up later with possible double entries in the archive. – user763539 Mar 25 '13 at 04:44

Saving deleted records to another table

1 Answers1