How to Create a Trigger in SQL Server for Use with CK

MS-DOS                | WINDOWS

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

1. From the Microsoft SQL Administrator, choose the Query button.

2. Type in the following in the query window:

         use pubs
   

3. Choose the Execute button.

4. Erase the information in the Query window.

5. 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
   
   

6. From the File menu, choose Save As and save the procedure as TRIGGER.SQL.

7. Choose the Execute button.

Setting Up FoxPro to Cause the Trigger to Be Invoked

Type in and run the following program:

   *****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