Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

sqlite triggers

by fionbarr (Monk)
on Mar 26, 2010 at 14:30 UTC ( #831163=perlquestion: print w/ replies, xml ) Need Help??
fionbarr has asked for the wisdom of the Perl Monks concerning the following question:

I am playing with SQLite and can't get the trigger to work...any pointers?
#!/usr/bin/perl # Copyright GPL (c) 2004 Mike Chirico mchirico@users.sourceforge.net m +chirico@comcast.net # 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;

Comment on sqlite triggers
Download Code
Re: sqlite triggers
by ctilmes (Priest) on Mar 26, 2010 at 14:24 UTC
    ... 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.
Re: sqlite triggers
by Illuminatus (Curate) on Mar 26, 2010 at 14:28 UTC
    OK, I'll bite - what does it do when you run it? Others might not see what you are seeing...

    fnord

Re: sqlite triggers
by zwon (Monsignor) on Mar 26, 2010 at 19:05 UTC

    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;");

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://831163]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (6)
As of 2014-07-12 02:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (238 votes), past polls