Re: [sqlite] Newby Question

[Simon Davies]

Hi Graham,

The trigger needs a where clause, as in the following:

sqlite>
sqlite> create table temp(      id integer primary key,
   ...>                         interested integer,
   ...>                         val1 text,
   ...>                         val2 text,
   ...>                         val3 text );
sqlite> create trigger temp_trigger
   ...>                 after update of interested on temp
   ...>                 begin
   ...>                         update temp set val1='newValue1',
   ...>                                         val2='val2NewValue',
   ...>                                         val3='val3NewValue'
where id=old.id;
   ...>                 end;
sqlite>
sqlite> insert into temp values( 1, 0, '1', '2', '3' );
sqlite> insert into temp values( 2, 0, '11', '22', '33' );
sqlite> insert into temp values( 3, 0, '111', '222', '333' );
sqlite> insert into temp values( 4, 0, '1111', '2222', '3333' );
sqlite>
sqlite> select * from temp;
1|0|1|2|3
2|0|11|22|33
3|0|111|222|333
4|0|1111|2222|3333
sqlite>
sqlite> update temp set interested=1 where id=2;
sqlite>
sqlite> select * from temp;
1|0|1|2|3
2|1|newValue1|val2NewValue|val3NewValue
3|0|111|222|333
4|0|1111|2222|3333
sqlite>

The use of 'old' and 'new' is covered on this sqlite documentation page:
http://www.sqlite.org/lang_createtrigger.html

Rgds,
Simon

On 28/08/07, Graham Wickens <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> I'm trying to learn SQLITE from a book and am making slow progress. My
> question is, in the following TRIGGER how do I tell it to only update
> the single record that the field Interested was updated? its a logical
> field either TRUE of FALSE, I want the trigger to work when the Field is
> TRUE.
>
>
> *create trigger* sbslogger* after update of *Interested* on *Aircraft *
> begin
> update *Aircraft* set *UserString1 *= Registration;
> update *Aircraft *set *UserString2* = 'PAGANHILL SBS';
> update *Aircraft* set *UserString3* = current_date;
> end;
>
> *the above works, but of course does the whole table ! and I am now stuck!
>
> thanks in anticipation
> **
>
> --
>        ¿¿¿
>       (ô ô)               [EMAIL PROTECTED]
> ----ooO-(_)-Ooo----   <http://www.westrowops.co.uk>
>    Wot! No SBS?
>
>
>

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------