Microsoft KB Archive/115113

{|
 * width="100%"|

-

The information in this article applies to:


 * Microsoft FoxPro Connectivity Kit, version 2.5
 * Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, 2.6
 * Microsoft FoxPro for MS-DOS, versions 2.5, 2.5a, 2.5b, 2.6

-

SUMMARY
A trigger is a special kind of stored procedure that is used to enforce integrity constraints. It is executed automatically whenever the user tries to modify data that the trigger has been designed to protect.

Using the FoxPro Connectivity Kit's DBExec function, you can cause a trigger to return an error message back to FoxPro using SQL Server's RAISERROR command.

Below are the procedures for setting up a trigger in SQL Server and returning an error to FoxPro using the Connectivity Kit.

MORE INFORMATION
WARNING: This code sample is provided on an "as-is" basis. This code sample was produced to answer commonly asked questions about usage of FoxPro commands, functions, and features to solve particular types of problems by illustrating a general approach that can be used to solve the problem. This code sample is not intended to be used within an application and is not supported by Microsoft Product Support Services. We make no warranty, either expressed or implied, including but not limited to implied warranties of merchantability and fitness for a particular purpose, with regard to this code sample or associated documentation.

For questions concerning the creation, syntax of, or functionality of a trigger, refer to the CREATE TRIGGER command in the SQL Server "Language Reference" or contact Microsoft SQL Server Product Support Services.

Setting Up a Trigger in SQL Server
 From the Microsoft SQL Administrator, choose the Query button.  Type in the following in the query window:       use pubs  Choose the Execute button. Erase the information in the Query window.  Type in the following procedure:       create trigger delauthor on authors for delete as        if (select count(*)             from deleted, titleauthor             where titleauthor.au_id = deleted.au_id) > 0 begin raiserror 99999 "Trigger: You can't delete an author                              that has a title" rollback transaction end  From the File menu, choose Save As and save the procedure as TRIGGER.SQL. Choose the Execute button.</li></ol>

Setting Up FoxPro to Cause the Trigger to Be Invoked
Type in and run the following program:  <pre class="CODESAMP">  *****SET THE LIBRARY AND INITIALIZE VARS IF _DOS SET LIBRARY TO SYS(2004)+"fpsql.plb" ELSE SET LIBRARY TO SYS(2004)+"fpsql.fll" ENDIF PUBLIC errval PUBLIC errmsg PUBLIC handle errval=0 errmsg=' ' sourcename= 'test' user= 'sa' passwd=''

********CONNECT handle=DBConnect(sourcename,user,passwd) IF handle > 0 WAIT WINDOW 'Successfully Connected' NOWAIT ELSE error=DBError(0,@errmsg,@errval) WAIT WINDOW STR(error)+' '+STR(errval)+' '+errmsg ENDIF

=DBSetOpt(handle,'Asynchronous',0) =DBSetOpt(handle,'BatchMode',1) =DBSetOpt(handle,'ConnTimeout',0) =DBSetOpt(handle,'Transact',1) =DBSetOpt(handle,'UseTable',0)

err=DBExec(handle,'use pubs') DO errhand WITH err,'USE PUBS'

**********Try to delete an author that has a title. **********Using the DBExec Function. **********This will activate that SQL Server's trigger **********which will cause an error to be returned.

sqlcomm= "delete authors where au_id='409-56-7008'" err=DBExec(handle,sqlcomm) DO errhand WITH err,"DBExec(handle,"+sqlcomm+")" IF err > 0 BROWSE ENDIF

**********DISCONNECT err=DBDisconn(handle) DO errhand WITH err,"DBDisconn" SET LIBRARY TO  CLOSE ALL

**********Error Handler Program PROCEDURE errhand PARAMETERS err,command IF err > 0 WAIT WINDOW ALLTRIM(UPPER(command))+"Completed Successfully"; NOWAIT ELSE WAIT WINDOW UPPER(command)+"NOT Completed Successfully" error=DBError(handle,@errmsg,@errval) WAIT WINDOW STR(error)+" "+STR(errval)+" "+errmsg ENDIF RETURN Note that an error occurs and the trigger returns the error specified with the RAISERROR command. Additional query words: FoxDos FoxWin ODBC CK

Keywords         : kbcode FxtoolCk Version          : 2.50 2.50a 2.50b 2.60 | 2.50 2.5 Platform         : MS-DOS WINDOWS Issue type       :
 * }