Re: [sqlite] Multiple WHENs in triggers

2012-02-28 Thread 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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple WHENs in triggers

2012-02-28 Thread 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
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

Re: [sqlite] Multiple WHENs in triggers

2012-02-28 Thread 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


On Tue, Feb 28, 2012 at 10:53 AM, hsymington  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

[sqlite] Multiple WHENs in triggers

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