Re: MSSQL: INSERT into table with TRIGGER

On 30/09/10 16:44, Ludwig, Michael wrote:
>> Did you use the do method or prepare/execute. If the former, 
>> try separating it into a prepare/execute.
> 
> I'm using prepare/execute.

Shame.

> A simple test case I wrote does work.

In which case I've no idea - sorry.

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

It sounds like it.
 
> 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

Can you put print statements in your real trigger and setup and odbc_err_handler to catch them so you can see what is really running?

There is an example in t/20SqlServer.t included in the DBD::ODBC test suite.

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com