Could your trigger be running into the syntax restriction described below? I'm not sure if the restriction actually applies to your circumstance -- not sure whether LIMIT and ORDER BY are invalid *anywhere* in a statement in a trigger that deletes a row, i.e. invalid also in a subquery, as you have done.
"The ORDER BY and LIMIT clauses on UPDATE<http://www.sqlite.org/lang_update.html> and DELETE <http://www.sqlite.org/lang_delete.html> statements are not supported. ORDER BY and LIMIT are not normally supported for UPDATE<http://www.sqlite.org/lang_update.html> or DELETE <http://www.sqlite.org/lang_delete.html> in any context but can be enabled for top-level statements using the SQLITE_ENABLE_UPDATE_DELETE_LIMIT<http://www.sqlite.org/compile.html#enable_update_delete_limit> compile-time option. However, that compile-time option only applies to top-level UPDATE<http://www.sqlite.org/lang_update.html> andDELETE <http://www.sqlite.org/lang_delete.html> statements, not UPDATE<http://www.sqlite.org/lang_update.html> and DELETE <http://www.sqlite.org/lang_delete.html> statements within triggers." http://www.sqlite.org/lang_createtrigger.html Regards Tim Romano Swarthmore PA On Mon, Aug 2, 2010 at 10:45 PM, liubin liu <7101...@sina.com> wrote: > > I created a table like this： > CREATE TABLE record_trip (trip_id CHAR(1), gp_no CHAR(1), rec_date INTEGER, > trun CHAR(1), ctrl_id CHAR(1), moment_value INTEGER, minutes_value INTEGER, > set_value INTEGER ); > CREATE UNIQUE INDEX i_record_trip ON record_trip (trip_id, gp_no, > rec_date); > > > And the trigger like： > CREATE TRIGGER trig1 before insert on record_trip > when (select count(*) from record_trip where trip_id=new.trip_id AND > gp_no=new.gp_no)>4 > begin > delete from record_trip where trip_id=new.trip_id AND gp_no=new.gp_no AND > rec_date=(select rec_date from record_trip where trip_id=new.trip_id and > gp_no = new.gp_no order by rec_date limit 1); > end; > > > I felt the when clause is wrong. It couldn't realize the intention of > executing the trigger after the num of trip_id and gp_no is larger than 4. > -- > View this message in context: > http://old.nabble.com/How-to-write-the-trigger--tp29331491p29331491.html > Sent from the SQLite mailing list archive at Nabble.com. > > _______________________________________________ > 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
