https://www.sqlite.org/lang_createtrigger.html
CREATE TRIGGER foo AFTER INSERT ON table
BEGIN
UPDATE table
SET field1 = field1 + 1
WHERE new.field1 IS NOT NULL
AND field1 > new.field1;
UPDATE table
SET field1 = (SELECT MAX(field1)+1 FROM table)
WHERE rowid = new.rowid AND new.field1 IS NULL;
END;
>-----Original Message-----
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Igor Korot
>Sent: Thursday, 31 October, 2013 20:16
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] TRIGGER syntax
>
>Igor,
>
>On Thu, Oct 31, 2013 at 5:33 PM, Igor Tandetnik <i...@tandetnik.org>
>wrote:
>> On 10/31/2013 8:22 PM, Igor Korot wrote:
>>>
>>> Hi, ALL,
>>> In the CREATE TRIGGER can I use something like this:
>>>
>>> CREATE TRIGGER foo AFTER INSERT
>>> BEGIN
>>> IF foo.field1 IS NULL
>>> UPDATE foo SET field1 = <some_value>;
>>> ELSE
>>> UPDATE foo SET field1 = field1 + 1 WHERE field1 >
>>> new.<value>;
>>> END;
>>>
>>> My concern is if CREATE TRIGER will understand conditionals.
>>
>>
>> No, there is no IF statement in SQLite.
>
>This is bad.
>
>> There are other ways to do
>> operations conditionally. I'd help you with the correct syntax, but I
>don't
>> understand what your example is supposed to achieve?
>
>And this is good. ;-)
>
>>
>> When you say foo.field1, which row of table foo do you want the value
>to
>> come from? When you say new.<value>, a value in which field of the
>newly
>> inserted row is this supposed to refer to? Perhaps you could explain
>what
>> you are trying to do in English, rather than making up some syntax that
>> doesn't make sense.
>
>What I'm looking for is to do a different processing based on the value
>that is
>inserted.
>When the record is inserted with the field1 as NULL, I want to have
>the field1 to have value max( field1 ) + 1.
>When the record inserted have some value in field1, i.e. field1 == 5,
>I want all records that have field1 > 5 to have field1 to be
>incremented. So if new record have field1 as 5, so the old record with
>field1 == 5 will have field1 = 6, field1 will become 7 and so forth.
>
>There is no trigger on the updating the record for that table.
>
>Thank you.
>
>> --
>> Igor Tandetnik
>>
>> _______________________________________________
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
