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;
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: sqlite triggers
by ctilmes (Vicar) on Mar 26, 2010 at 14:24 UTC | |
Re: sqlite triggers
by zwon (Abbot) on Mar 26, 2010 at 19:05 UTC | |
Re: sqlite triggers
by Illuminatus (Curate) on Mar 26, 2010 at 14:28 UTC |
Back to
Seekers of Perl Wisdom