For a start, the trigger references more tables than is provided in this schema. The following tables are missing from the schema: AICC_comments AICC_interactions AICC_core_detail
If I remove those tables from the trigger, the trigger successfully runs on both SQLite 2 and SQLite 3. Christian On Thu, 30 Sep 2004, Carfield Yim wrote: >Sure I can provide the schema: > > >create table AICC_objectives_status ( > session_id nvarchar(12) not null, > j_id varchar(255) null, > j_status nvarchar(13) null, > j_score nvarchar(20) null >) >; > > > > > >create table AICC_student_data ( > session_id nvarchar(12) not null, > attempt_number int null, > tries int null, > status nvarchar(15) null, > score nvarchar(20) null, > time nvarchar(12) null >) >; > > > > > >create table AICC_student_preferences ( > session_id nvarchar(12) not null, > audio int null, > language nvarchar(255) null, > lesson_type nvarchar(10) null, > speed int null, > text int null, > text_color nvarchar(30) null, > text_location nvarchar(20) null, > text_size nvarchar(20) null, > video nvarchar(15) null, > constraint PK_AICC_STUDENT_PREFERENCES primary key (session_id) >) >; > > > > > >create table AICC_student_preferences_1 ( > session_id nvarchar(12) not null, > extension int not null, > window nvarchar(20) null, > constraint PK_AICC_STUDENT_PREFERENCES_1 primary key (session_id, >extension) >) >; > > >create table AICC_core ( > session_id nvarchar(12) not null, > userid nvarchar(15) not null, > student_id nvarchar(15) not null, > courseid nvarchar(15) null, > lesson_status nvarchar(3) null, > lesson_location varchar(255) null, > score1 nvarchar(20) null, > time1 nvarchar(13) null, > lesson_mode nvarchar(40) null, > core_lesson text null, > totalnAttempts int null, > totalSeconds int null, > lastAttemptDate datetime null, > highestscore nvarchar(20) default 'NA' >null, > final_status nvarchar(1) null, > finish_date datetime null, > entry char(1) default 'a' not >null, > itemID nvarchar(15) null, > totalTime nvarchar(13) null, > totalTimeAtLastLaunch nvarchar(13) null, > testInstanceID nvarchar(12) null, > license_time_seconds int default 0 null, > license_trip_date datetime null, > constraint PK_AICC_CORE primary key (session_id) >) >; > > > ''~`` > ( o o ) >+------------------.oooO--(_)--Oooo.------------------+ >We are limited, not by our abilities, but by our vision. >| | >| http://www.carfield.com.hk | >| .oooO | >| ( ) Oooo. | >+---------------------\ (----( )--------------------+ > \_) ) / > (_/ > >On Wed, 29 Sep 2004, Christian Smith wrote: > >> Without the schema, it's difficult to say where the problem is. >> >> Check spelling and make sure session_id is a column in AICC_core. >> >> Christian >> >> PS. If you're sure AICC_core has session_id, but don't want to publish >> your schema for any reason, try reproducing the problem with an >> example schema you can publish. >> >> >> On Wed, 29 Sep 2004, Carfield Yim wrote: >> >> >Have create a trigger like this: >> > >> >create trigger AICC_core_del delete on AICC_core >> >begin >> > delete from AICC_objectives_status where session_id=old.session_id; >> > delete from AICC_comments where session_id=old.session_id; >> > delete from AICC_interactions where session_id=old.session_id; >> > delete from AICC_student_data where session_id=old.session_id; >> > delete from AICC_student_preferences where session_id=old.session_id; >> > delete from AICC_student_preferences_1 where session_id=old.session_id; >> > delete from AICC_core_detail where session_id=old.session_id; >> >end >> >; >> > >> >But get the following exception: >> > >> >08:03:46.046 create trigger AICC_core_del delete on AICC_core begin >> > delete from AICC_objectives_status where session_id=old.session_id >> >08:03:47.047 SQLite.Exception: near "session_id": syntax error >> >08:03:47.047 delete from AICC_comments where session_id=old.session_id >> >08:03:47.047 SQLite.Exception: no such column: old.session_id >> >08:03:47.047 delete from AICC_interactions where session_id=old.session_id >> >08:03:47.047 SQLite.Exception: no such column: old.session_id >> >08:03:47.047 delete from AICC_student_data where session_id=old.session_id >> >08:03:47.047 SQLite.Exception: no such column: old.session_id >> >08:03:47.047 delete from AICC_student_preferences where >> >session_id=old.session_id >> >08:03:47.047 SQLite.Exception: no such column: old.session_id >> >08:03:47.047 delete from AICC_student_preferences_1 where >> >session_id=old.session_id >> >08:03:47.047 SQLite.Exception: no such column: old.session_id >> >08:03:47.047 delete from AICC_core_detail where session_id=old.session_id >> >08:03:47.047 SQLite.Exception: no such column: old.session_id >> >08:03:47.047 end >> >08:03:47.047 SQLite.Exception: cannot commit - no transaction is active >> > >> >Anyone know how to solve.... the manual tell that the old is a valid >> >reference in delete event... but see it is not? >> > >> >> -- >> /"\ >> \ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL >> X - AGAINST MS ATTACHMENTS >> / \ >> > -- /"\ \ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
