Transactions, Commits and Rollbacks

[Ruslan Zasukhin]

On 10/12/13 7:51 AM, "Ivan Smahin" <ivan_smahin at paradigmasoft.com> wrote:

>> Just an example would be: If I had a database that had invoices in it and I
>> want to delete all the records from the database
>> for year 1980. The invoices are stored in one table and each line item on the
>> invoice is stored in another table. I need to be
>> sure that when an invoice is deleted the line items for that invoice are also
>> deleted. Of course the key for each invoice is stored as a field
>> in the invoice items table to perform the delete on.
>> 
>> This is easily done with transactions, but I'm kind of clueless right now of
>> the best way to achieve it with Valentina.
>> 
>> I wonder if a delete trigger might work reliably?
>> 
>> Any help or ideas you may have are much appreciated.
>> 
>> I don't usually need this type of query often, but when this type of task is
>> needed it is usually deleting many records.
>> 
> 
> 
> You have few ways to do it.
> 1. You can use link's cascade deleting feature.

I'd say this is the best way to have integrity of links.


====
SQLite many years did use triggers to provide integrity of FKs,
But this was only because there was no Foreign Keys.
In 3.x of SQLite was added FK, and it should be used also instead of
triggers.

Triggers should be avoided if possible,
They work like invisible daemons :-)


> 2. OnDelete triggers.
> 3. Stored procedure way - to make any complex operation "atomic".
> 
> All of that ways are common for almost any RDBMS.
> It is not directly related to transactions.

-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]