Hello,
I'm having problems catching the error and error message sent
by a database trigger using raiserror.
Here is my code:
....
$dbh = _SP_connect(); # Connect
if ($dbh) { # Connection OK
my $sth = $dbh->prepare($sp_call); # Prepare execution
$sth->execute; # Execute
do {
while($d = $sth->fetch) { # For items found
if ($sth->{syb_result_type} == 4042) { # param result
if ($retval != 0) { # If error
$retmsg = $d->[0]; # return err string
}
}
elsif ($sth->{syb_result_type} == 4043) { # STATUS result
$retval += $d->[0]; # This value says
}
}
} while($sth->{syb_more_results});
_SP_disconnect($dbh);
if ($retval) {
$$rerrmsg = "$fn_name -- ". $retmsg;
return $retval + $returnCodes{$fn_name};
}
....
I need help! I can't find any documentation on this. With this
code the raiserror from the trigger is ignored and the command
is interpreted as successful
Help much appreciated,
Sonia
P.S. This is the code of the TRIGGER
create trigger td_doctype on DOC_TYPE for DELETE as
begin
declare
@numrows int,
@errno int,
@errmsg varchar(255),
@doc_type_id int
select @numrows = @@rowcount
if @numrows = 0
return
/* */
declare cur cursor
for select doc_type_id from deleted
open cur
fetch cur into @doc_type_id
while @@sqlstatus = 0
begin
/* Test if the decocument type is used in VALID_DEC_CLASS */
if (select count(*)
from VALID_DEC_CLASS
where doc_type_id = @doc_type_id) != 0
begin
select @errno = 30002,
@errmsg = 'The document type is referred in table
VALID_DEC_CLASS.'
goto error
end
/* Get next value */
fetch cur into @doc_type_id
end
close cur
deallocate cursor cur
/* End with no error */
return
/* Errors handling */
error:
rollback trigger with raiserror @errno @errmsg
end
go
_______________________________
Sonia Lodovichetti, Eng.
Software Engineer, LMC / I / MED
Ericsson Canada Inc.
Tel: +1 (514) 345 7900 x5327
Fax: +1 (514) 345 7979
Mailto:[EMAIL PROTECTED]
