RE: MSSQL: INSERT into table with TRIGGER

[Ludwig, Michael]

> Did you use the do method or prepare/execute. If the former, 
> try separating it into a prepare/execute.

I'm using prepare/execute.

A simple test case I wrote does work.

The problem must be with the specifics of my
insert/trigger/procedure/xml/update scenario.

Here's the simple test case:

==== SQL ====

create table tt1 (a int);
create table tt2 (b int);
create trigger trig_tt1 on tt1
for insert
as begin
  insert into tt2 select a from Inserted
end
-- Testing it works:
insert into tt1 values (77);
select * from tt2;

==== Perl ====

use strict;
use warnings;
use DBI;

my $dsn = 'dbi:ODBC:testikowski';
my %opt = (PrintError => 0, RaiseError => 1, AutoCommit => 1);
my $dbh = DBI->connect( $dsn, 'usr', 'secret', \%opt );

my $count = 0;
my $show = sub {
  $count++;
  my $rows = $dbh->selectcol_arrayref('select b from tt2');
  print "($count) @$rows\n";
};

$show->();

#$dbh->begin_work;
my $sth_I = $dbh->prepare( 'insert into tt1 (a) values (?)' );
my $ret = $sth_I->execute( time );
#$dbh->commit;
print "insert: $ret\n";

$show->();

$dbh->disconnect;

print STDERR $DBI::VERSION, "\n";
print STDERR $DBD::ODBC::VERSION, "\n";

__END__
Michael Ludwig