TRIGGER on SQLLite

I ask a little help as far the trigger in SqlLite

I have two tables :

  1. name ‘righe’ with fields:
  • codconto
  • codicecf
  • dareflag
  • importo
  • esercizio
  1. name ‘saldi’ with fields:
  • esercizio
  • conto
  • clientefornitore
  • dare
  • avere
  • saldo

when I add a record in the table righe I need to update (or insert if the record is missing) the table saldi

righe ========== saldi
righe.codconto = saldi.conto
righe.codicecf = saldi.clientefornitore
righe.esercizio = saldi.esercizio

if righe.dareflag = ‘D’
saldi.dare = saldi.dare + righe.importo
saldi.saldo = saldi.saldo + righe.importo

if righe.dareflag = ‘A’
saldi.avere = saldi.avere + righe.importo
saldi.saldo = saldi.saldo - importo

when I delete a record in table righe update only the row in table saldi

if righe.dareflag = ‘D’
saldi.dare = saldi.dare - righe.importo
saldi.saldo = saldi.saldo - righe.importo

if righe.dareflag = ‘A’
saldi.avere = saldi.avere - righe.importo
saldi.saldo = saldi.saldo + importo

I’m trying for 3 days, but I’m not good with triggers.
I could not do anything.
Sorry for my english … is really worth

Definitely review http://sqlite.org/lang_createtrigger.html

You’ll probably need to create 2 triggers
One for the INSERT and one for UPDATE

In an insert trigger you get one pseudo table called “NEW” that has the new values being inserted.
You can check the value of the columns in “new” and the run an insert/delete or update statement based on those values.
BUT triggers are not full fledged applications and SQLIte’s trigger language is somewhat limited.

And you can insert into whatever tables using select statements IF that’s appropriate
But you WILL probably have to do inserts / updates etc using just select statements - because sqlites trigger language is kind of limited

So the insert trigger might be
CREATE TRIGGER insert_saldo INSERT ON righe
BEGIN
insert into righe ( saldi.conto, saldi.clientefornitore, saldi.esercizio ) select new.conto, new.clientefornitore, new.esercizio from new
END;

I don’t quite understand the table structure, which is probably because I don’t speak Italian! However, I’m not sure why you have three values shared between righe and saldi (codconto/conto, codicecf/clientefornitore, esercizio/esercizio). Usually what I’d expect here is to have a single relationship between the two tables.

I’m going to give a slightly different example which I think should help you, and then you can adapt it to your own case.

Let’s say we have two tables: products and categories.
Products contains ProductID, ProductName, ProductPrice, NumberInStock, Category
Categories contains CategoryName, TotalValueInStock

I’m also going to assume that categories is a table which users can’t edit themselves, so I’m going to be populating it based on a trigger.

You want something like this:

// To create the category when a new one is added
CREATE TRIGGER ProductInsert AFTER INSERT ON Product
WHEN (SELECT COUNT(CategoryName) FROM Categories WHERE CategoryName=New.Category)=0
BEGIN
INSERT INTO Categories (CategoryName, TotalValueInStock) VALUES (New.Category, New.ProductPrice*New.NumberInStock);
END;

//To update an existing one after an addition
CREATE TRIGGER ProductInsert2 AFTER INSERT ON Product
WHEN (SELECT COUNT(CategoryName) FROM Categories WHERE CategoryName=New.Category)!=0
BEGIN
UPDATE Categories SET TotalValueInStock = TotalValueInStock + New.ProductPrice*New.NumberInStock WHERE CategoryName = New.Category;
END;

//To update an existing one after a deletion
CREATE TRIGGER ProductDelete AFTER DELETE ON Product
BEGIN
UPDATE Categories SET TotalValueInStock = TotalValueInStock - New.ProductPrice*New.NumberInStock WHERE CategoryName = New.Category;
END;

These three are just simple examples; note the use of WHEN after you have defined when the trigger should run, which is how I distinguish between inserting a new row or updating an existing one in Category.

Within the body of the trigger you can also use CASE statements. So, for example, you could do something like this:

UPDATE Categories SET TotalValueInStock = TotalValueInStock +
CASE WHEN New.ProductPrice = 1
THEN New.ProductPrice*New.NumberInStock
ELSE (New.ProductPrice * New.NumberInStock) + 20
END
WHERE CategoryName = New.Category;

(Note that that’s just an example and I can’t see any real-world application of doing that, but it gives you an idea of the syntax.)

Hopefully this’ll help you.

I receive error on use :
CREATE TRIGGER ultimavend AFTER INSERT ON righedocumenti
WHEN (SELECT COUNT(codice) FROM articoli WHERE codice=New.codicearticolo AND new.ciclo = 1)!=0
BEGIN
UPDATE articoli SET dataultvend = new.datadocumento, clienteultvend = new.clientefornitore,
prezzoultvend = new.prezzounitario, scontoultvend = new.sconto, totaleultvend = new.imponibile
WHERE codice = New.codicearticolo AND dataultvend <= New.datadocumento;
END

can you help ?

What’s the error? That would help narrow the cause down.

You might need a semicolon after the END.

No, was a problem with RazorSQL, the trigger in sqlite manager plugin via firefox work fine !
Thanks!