2009/8/27 Jarod Tang <jarod.t...@gmail.com>:
> Hi List users,
>
> In my code, i create two triggers (Trigger_A, Trigger_B) on a table (
> sample_db), but after execution, it's found that Trigger_A is not executed
> correctly ( old.msg_box hasnt be put into table log ). And it seems to me
> that SqlLite invalids the OLD reference after it found it be referred in as
> SET statement, so that it cant be get used by later triggers, or are there
> else explanation for this?
>
> Thanks,
> -- Jarod
>
> code snips as follows
> A:
> db.execSQL("CREATE TRIGGER Trigger_A + AFTER UPDATE OF msg_box ON sample_db
> WHEN ( condition) +
> "BEGIN " +
> " INSERT INTO Log ( old.msg_box )"
invalid syntax ( no "VALUES" keyword ).
> "END;");
> B:
> db.execSQL("CREATE TRIGGER Trigger_B + AFTER UPDATE OF msg_box ON sample_db
> WHEN ( condition) +
> "BEGIN " +
> " UPDATE sample_db SET msg_box = -old.msg_box " +
> " WHERE _id = old._id; " +
> "END;");
>
>
Using the sqlite shell:
SQLite version 3.6.16
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table tst( id integer primary key, data text );
sqlite>
sqlite> create table log( data text );
sqlite>
sqlite> create trigger trig2 after update on tst begin update tst set
data = old.data where id=old.id; end;
sqlite> create trigger trig1 after update on tst begin insert into log
values( old.data ); end;
sqlite>
sqlite> insert into tst( data ) values( 'tstRow1' );
sqlite> insert into tst( data ) values( 'tstRow2' );
sqlite> insert into tst( data ) values( 'tstRow3' );
sqlite>
sqlite> update tst set data='updatedTstRow2' where id=2;
sqlite>
sqlite>
sqlite> select * from log;
tstRow2
updatedTstRow2
sqlite>
sqlite>
sqlite> select * from tst;
1|tstRow1
2|tstRow2
3|tstRow3
sqlite>
All seems to work ok.
Regards,
Simon
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
