On 28 Aug 2009, at 3:27am, Dennis Volodomanov wrote:
> CREATE TRIGGER CountTypeA AFTER INSERT ON abc /* when abc.TypeID ==
> 1 */ BEGIN
> UPDATE abcCount SET TypeCountA=TypeCountA+1; END
>
> CREATE TRIGGER CountTypeB AFTER INSERT ON abc /* when abc.TypeID ==
> 2 */ BEGIN
> UPDATE abcCount SET TypeCountB=TypeCountB+1; END
On 28 Aug 2009, at 3:33am, Dennis Volodomanov wrote:
>> Instead of keeping your total in abcCount, make another table
>> especially for counts
>>
>> countType number
>> abc 27
>> def 14
>>
>> and use the TRIGGER to update that table.
>
> I could do that, but I still don't know when to update abc and when
> to update def, based on the value being inserted.
countOfTypesInabc:
TypeID count
1 27
2 14
CREATE TRIGGER countOfTypesInabc AFTER INSERT ON abc
BEGIN
UPDATE countOfTypesInabc SET count = count+1 WHERE TypeID = new.TypeID;
END
Don't forget to make the equivalent TRIGGER for when you delete a
record from abc, and another one for when an UPDATE a record in abc,
changing the TypeID from one value to another, if that every happens.
You will have to add something for creating records in the table
countOfTypesInabc if they don't already exist, but I'll leave that to
you to work out.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
