RE: Trigger errors

Thanks for the tip and the note on the redundant dereference.

Fred

> -----Original Message-----
> From: [email protected] 
> [mailto:[email protected]] 
> Sent: Tuesday, February 14, 2006 2:29 PM
> To: Strauss, Fred
> Cc: [email protected]
> Subject: Re: Trigger errors
> 
> There are various possibilities.
> 
> The first is that you return an error code from your stored proc (ie.
> RETURN 1), and then check this return status 
> (CS_STATUS_RESULT). If it is
> non-0 then you have an error condition. In general I've 
> tended to return
> the same value that I use in the RAISERROR.
> 
> The second is that you trap errors in the server callback and 
> raise the
> error internally that way.
> 
> I also noted that you use $$db_ref for the database handle - 
> this shouldn't
> be necessary as a database handle is already a reference...
> 
> Michael
> 
> 
> 
> 
> Internet
> [email protected]@peppler.org - 14/02/2006 14:16
> 
> 
> Sent by:    [email protected]
> 
> To:    sybperl-l
> 
> cc:
> 
> 
> Subject:    Trigger errors
> 
> 
> 
> Is there a  "recommended" way to trap errors generated by 
> triggers assuming
> the trigger is  using raiserror to signal a problem?
> 
> I know that given  the following in my trigger:
> 
> error:
>         select @msgText =  "Related Key -" + @fieldname +  "- "  +
>                  "does not exist in the table " + @desttablename  +
>                  " attempt to insert invalid foreign key into 
> table - "  +
>                  @sourcetablename + " -Failed "
>          raiserror 30053 @msgText
>         rollback  tran
>          return
> 
> that I will get a callback in my client message callback 
> function but  the
> question is how do I indicate to the calling perl script that 
> a problem
> occurred?
> 
> I have the following routine that executes the stored proc in 
>  question:
> 
> sub  exec_sql_nores {
>         my $db_ref =  shift;
>         my $sql =  shift;
>         my ($row_count,  $result_type, $status);
> 
>         # establish a handle to the  database
>         $row_count =  $$db_ref->ct_execute($sql);
>          while (($row_count = $$db_ref->ct_results($result_type)) ==
> CS_SUCCEED)  {
>                  if ($result_type == CS_CMD_FAIL)  {
>                          printf STDERR "Error in SQL: %s\n",  $sql;
>                  }
>                  next unless  $$db_ref->ct_fetchable($result_type);
>                  my  %data;
>                  while (%data = $$db_ref->ct_fetch(CS_TRUE))  {
>                          ;
>                  };
>          }
>         return  $row_count;
> }
> 
> This routine returns CS_END_RESULTS (-205) as it would if the 
>  stored proc
> completed successfully.
> 
> 
> Is it a matter  of looking at the error code raised by 
> raiserror and then
> returning CS_FAIL from  the message callback?
> 
> Thanks for any  help!
> 
> Fred
> 
> 
> 
> The information contained in this communication is intended 
> for the use of
> the designated recipients named above.
> If the reader of this communication is not the intended 
> recipient, you are
> hereby notified that you have received this communication in 
> error, and
> that any review, dissemination, distribution or copying of this
> communication is strictly prohibited.
> If you have received this communication in error, please notify The
> Associated Press immediately by telephone at +1-212-621-1898 
> and delete
> this email. Thank you.
> 
> 
> 
> This message and any attachments (the "message") is
> intended solely for the addressees and is confidential. 
> If you receive this message in error, please delete it and 
> immediately notify the sender. Any use not in accord with 
> its purpose, any dissemination or disclosure, either whole 
> or partial, is prohibited except formal approval. The internet
> can not guarantee the integrity of this message. 
> BNP PARIBAS (and its subsidiaries) shall (will) not 
> therefore be liable for the message if modified. 
> 
>                 ---------------------------------------------
> 
> Ce message et toutes les pieces jointes (ci-apres le 
> "message") sont etablis a l'intention exclusive de ses 
> destinataires et sont confidentiels. Si vous recevez ce 
> message par erreur, merci de le detruire et d'en avertir 
> immediatement l'expediteur. Toute utilisation de ce 
> message non conforme a sa destination, toute diffusion 
> ou toute publication, totale ou partielle, est interdite, sauf 
> autorisation expresse. L'internet ne permettant pas 
> d'assurer l'integrite de ce message, BNP PARIBAS (et ses
> filiales) decline(nt) toute responsabilite au titre de ce 
> message, dans l'hypothese ou il aurait ete modifie.
> 
>