You description is inconsistent with your example.
The original trigger matches the "problem description" and the one immediately
hereunder matches your transformation example (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.)
The statement "field1 > 5" is inconsistent with the "So if new record have
field1 as 5, so the old record with field1 == 5 will have field1 = 6", which
implies you really meant to say:
>> 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 == 6 will become 7 and so
>> forth.
A clear and concise problem statement is a necessary prerequisite to devising
an implementation ...
CREATE TRIGGER foo AFTER INSERT ON table
BEGIN
UPDATE table
SET field1 = field1 + 1
WHERE new.field1 IS NOT NULL
AND field1 >= new.field1
AND rowid <> new.rowid;
UPDATE table
SET field1 = (SELECT MAX(field1)+1 FROM table)
WHERE rowid = new.rowid AND new.field1 IS NULL;
END;
>-----Original Message-----
>From: Keith Medcalf [mailto:kmedc...@dessus.com]
>Sent: Thursday, 31 October, 2013 21:27
>To: 'General Discussion of SQLite Database'
>Subject: RE: [sqlite] TRIGGER syntax
>
>
>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
