[prev in list] [next in list] [prev in thread] [next in thread] 

List:       dbi-dev
Subject:    DBI - Raiserror
From:       "Sonia Lodovichetti (LMC)" <Sonia.Lodovichetti () ericsson ! ca>
Date:       2001-11-16 17:30:46
[Download RAW message or body]


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:sonia.lodovichetti@ericsson.ca

[prev in list] [next in list] [prev in thread] [next in thread]