> I can't
> believe that SQLite only allows only one trigger with only one WHEN per
> action... can it?
SQLite allows only one WHEN per trigger but any number of triggers per
action (without any defined order of execution). So you can do it like
this:
CREATE TRIGGER UpdateSaleItemDescription1 AFTER INSERT ON SaleItem
WHEN NEW.SaleItem_ID='7'
BEGIN
UPDATE SaleItem SET SaleItem_Description='This is seven' WHERE
SaleItem_ID=NEW.SaleItem_Id;
END;
CREATE TRIGGER UpdateSaleItemDescription2 AFTER INSERT ON SaleItem
WHEN NEW.SaleItem_ID<>'7'
BEGIN
UPDATE SaleItem SET SaleItem_Description='This is not seven' WHERE
SaleItem_ID=NEW.SaleItem_Id;
END;
Or you can also do it like this:
CREATE TRIGGER UpdateSaleItemDescription AFTER INSERT ON SaleItem
BEGIN
UPDATE SaleItem SET SaleItem_Description='This is seven' WHERE
SaleItem_ID=NEW.SaleItem_Id
AND NEW.SaleItem_ID='7';
UPDATE SaleItem SET SaleItem_Description='This is not seven' WHERE
SaleItem_ID=NEW.SaleItem_Id
AND NEW.SaleItem_ID<>'7';
END;
Pavel
On Tue, Feb 28, 2012 at 10:53 AM, hsymington <i...@hamishsymington.com> wrote:
>
> Hi all,
>
> I'm trying to put in a series of conditionals into a trigger, along the
> lines of
>
> --
> CREATE TRIGGER UpdateSaleItemDescription AFTER INSERT ON SaleItem
>
> WHEN NEW.SaleItem_ID='7'
> BEGIN
> UPDATE SaleItem SET SaleItem_Description='This is seven' WHERE
> SaleItem_ID=NEW.SaleItem_Id;
> END;
>
> WHEN NEW.SaleItem_ID<>'7'
> BEGIN
> UPDATE SaleItem SET SaleItem_Description='This is not seven' WHERE
> SaleItem_ID=NEW.SaleItem_Id;
> END;
>
>
> INSERT INTO SaleItem(SaleItem_TaxRate) VALUES('0');
> INSERT INTO SaleItem(SaleItem_TaxRate) VALUES('0');
> --
>
> (obviously simplified for ease of description here).
>
> But this isn't working - I get a syntax error near WHEN (the second one). If
> I remove the four lines starting WHEN NEW.SaleItem_ID='8' then it works ok.
>
> Can I use a SELECT CASE statement here? If so, what's the syntax? I can't
> believe that SQLite only allows only one trigger with only one WHEN per
> action... can it?
>
> Thanks in advance,
>
> Hamish
>
> --
> View this message in context:
> http://old.nabble.com/Multiple-WHENs-in-triggers-tp33407606p33407606.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
