Hi,
I am facing a very weird issue with SQLite's triggers.
I have a database having two tables whose schema is defined below:
create table table1 ( id integer primary key );
insert into table1 values (-1);
create table table2 (
id integer primary key,
no integer,
hidden integer,
start_time integer
);
I have the following 'AFTER INSERT' trigger that updates 3 fields of the
table using an update statement:
create trigger set_id_and_no_table2 after insert on table2
where no = -1 and hidden = 1
begin
update table1 set id = id + 1;
update table2 set id = (select id from table1), no = (select id from
table1), start_time = strftime('%s', 'now') where id = -1;
end;
I have a perl script that inserts the record in the table2.
use DBI;
$dbh = DBI->connect('dbi:SQLite:records', '', '');
$dbh->do('insert into table2(id, no, hidden) values (-1, -1, 1)');
Now when I execute this script, this is what I get when I query the 'table2'
table.
#perl insert_record.pl
#sqlite3 records
SQLite version 3.3.13
Enter ".help" for instructions
sqlite> select * from table2;
0|-1|1|1340038692 <- Here the value should be 0 instead of -1 in
second column
Another insert into table,
#perl insert_record.pl
#sqlite3 records
SQLite version 3.3.13
Enter ".help" for instructions
sqlite> select * from table2;
0|-1|1|1340038692 <- Here the value should be 0 instead of -1 in
second column
1|-1|1|1340038723 <- Here the value should be 1 instead of -1 in
second column
The problem above is that the trigger is updating only fields id and
start_time of the table 'table2' whereas its not able to update 'no' field,
its always '-1'.
Now to add to the weirdness, if I insert a record in table2 using sqlite
prompt, then the 'no' field is also getting updated.
#sqlite3 records
SQLite version 3.3.13
Enter ".help" for instructions
sqlite> insert into table2(id, no, hidden) values (-1, -1, 1);
sqlite> select * from table2;
0|-1|1|1340038692
1|-1|1|1340038723
2|2|1|1340038789 <- Here the value is correct i.e. 2 in the second
column
Any idea what could be wrong here.
I am really stuck due to this.
Thanks,
Prashant
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
