[sqlite] Multiple WHENs in triggers

[hsymington]

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...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Pavel Ivanov]

> 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

[hsymington]

Pavel Ivanov-2 wrote:
>
>>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:
>

Thanks, Pavel, that's just what I needed to know. I think I'd been got by
other languages only allowing one trigger per type.

Hamish
--
View this message in context: http://old.nabble.com/Multiple-WHENs-in-triggers-tp33407606p33407758.html

[Igor Tandetnik]

On 2/28/2012 11:05 AM, Pavel Ivanov wrote:
> 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;
>

Or like this:

UPDATE SaleItem SET SaleItem_Description=
(case when NEW.SaleItem_ID='7' then 'This is seven' else 'This is not
seven' end)
WHERE SaleItem_ID=NEW.SaleItem_Id;

--
Igor Tandetnik