sqlite triggers

[fionbarr]

I am playing with SQLite and can't get the trigger to work...any pointers?

#!/usr/bin/perl
# Copyright GPL (c) 2004 Mike Chirico [e-mail] m
[e-mail]


# You will need the following to work with
# SQLite3
#
#  $ perl -MCPAN -e shell
#  cpan> install DBI
#  cpan> install DBD::SQLite


# Reference
#  http://www.perl.com/pub/a/2004/09/12/embedded.html

use strict;
use warnings;

use DBI;

my $database = "test_database";

my $dbh = DBI->connect( "dbi:SQLite:$database" ) || die "Cannot connec
+t: $DBI::errstr";

$dbh->do("DROP TABLE user");
$dbh->do("DROP TABLE profile");
$dbh->do("DROP TABLE demographic");

$dbh->do( "CREATE TABLE user (
                    email       VARCHAR(25),
                    user_id     VARCHAR(15),
                    perm        VARCHAR(30))");

$dbh->do( "CREATE TABLE profile (
                    user_id     VARCHAR(15),
                    pref        VARCHAR(30),
                    ic_ind      INTEGER)");

$dbh->do( "CREATE TABLE demographic (
                    user_id     VARCHAR(15),
                    fname       VARCHAR(15),
                    lname       VARCHAR(20),
                    interest    INTEGER)");

$dbh->do("DROP TRIGGER update_profile");

$dbh->do("CREATE TRIGGER update_profile AFTER INSERT  ON user
BEGIN
  INSERT INTO profile user_id values (new.user_id);
END;");

$dbh->do( "INSERT INTO user (email, user_id, perm)
                        values ('abc\@test.net','a12345','perm_data_1'
+)");

$dbh->do( "INSERT INTO user (email, user_id, perm)
                        values ('nancy\@optonline.net','a131313','perm
+_data_2')");

$dbh->do( "INSERT INTO user (email, user_id, perm)
                        values ('naomi\@gmail.com','b515515','perm_dat
+a_3')");

$dbh->do( "INSERT INTO profile (user_id, pref,ic_ind)
                        values ('b515515','sex','whatever')");

$dbh->do ("INSERT INTO profile (user_id, pref, ic_ind)
                        values ('a12345', 'talking', 'whatever')");

$dbh->do ("INSERT INTO demographic (user_id, fname, lname, interest)
                        values ('a12345', 'Nancy', 'McGrath','whatever
+')");

print "LAST insert id: ",$dbh->func('last_insert_rowid'),"\n\n\n";

my $i;
print "dumping profile\n";
my $res = $dbh->selectall_arrayref( q( SELECT * FROM profile ));
foreach( @$res ) {
    foreach $i (0..$#$_) {
       print "$_->[$i] "
       }
    print "\n";
}

print "\ndumping user\n";
$res = $dbh->selectall_arrayref( q( SELECT email, user_id, perm FROM u
+ser ));
foreach( @$res ) {
    foreach $i (0..$#$_) {
       print "$_->[$i] "
       }
    print "\n";
}

print "\ndumping demographic\n";
$res = $dbh->selectall_arrayref( q( SELECT * FROM user ));
foreach( @$res ) {
    foreach $i (0..$#$_) {
       print "$_->[0] "
       }
    print "\n";
}

$dbh->disconnect;

[ctilmes]

  ...
  INSERT INTO profile user_id values (new.user_id);
  ...

Try putting () around the keys you are inserting.

  ...
  INSERT INTO profile (user_id) values (new.user_id);
  ...

This is really an SQL question. Try running "sqlite" on the command line and get your SQL syntax right before jumping into Perl. It is easier to get error messages there too.

[zwon]

There's syntax error, it should be:

$dbh->do("CREATE TRIGGER update_profile AFTER INSERT  ON user
BEGIN
  INSERT INTO profile (user_id) values (new.user_id);
END;");

[Illuminatus]

OK, I'll bite - what does it do when you run it? Others might not see what you are seeing...

fnord