Access command button to invoke a trigger in SQL can it be done

[bherring]

I have a form called allpayments that allows for data entry to a table called CUST_PAYMENTS. On the form I only have certain fields from the table. PAT_ID, TRNSDT, TRNSCD, AMNT. now in SQl I have a trigger called add_to_total2.

The trigger on insert adds the AMNT to the total payment in another table. My issue is when the users enter data into the table. It is being uploaded to the SQl tables via the ODBC connection, but it is not triggering the trigger. So I need to write a code on a command button on the form that will invoke the trigger in SQL to run. any help.

[vb5prgrmr]

Sounds to me like you don't have the trigger configured correctly... What you will need to do is to configure this trigger correctly and the best place to do that is in a SQL Forum. There is one at tek-tips.com and a few others around and they should be able to help you configure your trigger correctly...



Good Luck

[bherring]

Sounds to me like you don't have the trigger configured correctly... What you will need to do is to configure this trigger correctly and the best place to do that is in a SQL Forum. There is one at tek-tips.com and a few others around and they should be able to help you configure your trigger correctly...



Good Luck

I put it hear because my SQL trigger is working after a manual insert on the SQl server. What is not working is that the Access Form data entry is not viewed as an insert by SQL so it is not hitting my trigger. I need to find some code that I can put behind a command button to hit my SQL trigger

[Jim Doherty]

I put it hear because my SQL trigger is working after a manual insert on the SQl server. What is not working is that the Access Form data entry is not viewed as an insert by SQL so it is not hitting my trigger. I need to find some code that I can put behind a command button to hit my SQL trigger

Use a stored procedure to insert your record and pass a parameter to it

[bherring]

Ok TABLES ARE VISITS and CUST_PAYMENTS

Fields are PAT_ID which is in both tables Total payments in the visit table

AMNT, TRNSCD, TRNSDT are in the CUST_PAYMENT table. the users will enter the patient ID the amount of the payment the trasnction cod and transaction date to the form in access. once a row has been added to the CUST_PAYMENT table I need the amount of that row to be added to the total payment field of the visit table where the PATID = the patient id in the cust_payment file. I had created a triger in SQL after insert update the visit table, but when the users enter data on the form SQL is not seeing it as an insert so the trigger is not being activated. can I create and insert query in access that will take the vaules of the form and insert them into the CUTS_PAYMENT table so that the trigger can be activated.?

[Jim Doherty]

Ok TABLES ARE VISITS and CUST_PAYMENTS

Fields are PAT_ID which is in both tables Total payments in the visit table

AMNT, TRNSCD, TRNSDT are in the CUST_PAYMENT table. the users will enter the patient ID the amount of the payment the trasnction cod and transaction date to the form in access. once a row has been added to the CUST_PAYMENT table I need the amount of that row to be added to the total payment field of the visit table where the PATID = the patient id in the cust_payment file. I had created a triger in SQL after insert update the visit table, but when the users enter data on the form SQL is not seeing it as an insert so the trigger is not being activated. can I create and insert query in access that will take the vaules of the form and insert them into the CUTS_PAYMENT table so that the trigger can be activated.?

bherring

I do not see why you need a trigger to be frank! the ODBC driver is not tuning into the trigger and my inclination would be to dump the trigger entirely in favour of methods that existed prior to the invention of those things - for which I am not a great fan myself.

Wherever possible I personally favour keeping processing on the server and stored procedures are all about keeping the processing precompiled and optimised on the server.

The idea is that you place your sequence code CRUD (create, read,update,del ete) in nice tidy little packages in stored procedures and make calls to them to do the work server side, as opposed to on the client. You are doing a mixture of both and many would argue that the simplest way is to just to fire off an update SQL statement in the 'afterupdate' event of textbox control servicing your AMNT form control and let it deal with the foreign table value accordingly and thats it!... job done!... not a trigger in sight and no complicated stored procedures either!

But given the client is not 'SQLing' properly and given you 'have' that trigger all you want to do is get it activated! that is the thrust of the thread, so that is why I mention a stored procedure. You are obviously working server side so this is my little contribution to your understanding as to what else there is out there 'other' than triggers.

If a stored procedure was to do the work then it obviously does it server side. This would activate the trigger because both routines are sitting on the same machine. In order to make the stored procedure do something meaningful in these circumstance you would have to pass/provide 'something' to it, to enable it to its work... and that is a parameter value.

Below is a stored procedure that takes a single parameter of the datatype INT namely your PATID value it then uses that value to UPDATE the AMNT amount in the PAT_VISIT table on the server, but only where the Patient ID field matches the PATID field in the CUST_PAYMENT table.

CREATE PROCEDURE dbo.usp_Update_Pat_Visit_Amount
(@PATID int)

AS
UPDATE PAT_VISIT
SET PAT_VISIT.[Total Payments]=CUST_PAYMENT.amnt
FROM PAT_VISIT, CUST_PAYMENT
WHERE CUST_PAYMENT.[PatID]=@PATID
GO

So how do you get this procedure working? heres how, firstly you create it on the server. If you created the trigger you can create this as well, just paste it into a new procedure window.

Now that it is created and you have set the relevant permissions on the server to use it, you want to call it from your frontend application. The easiet way to call it is to create a simple 'passthrough' query and save it.

The passthrough query is a conventional query having an SQL property. It is that SQL property text string that we can manipulate whereby we 'pass' a parameter to it replacing the @PATID section of the string with a value in code.

So.. next step....create that passthrough query and in the SQL window just paste this into it and save it as qryPassthrough and then paste the following into its SQL window save and close the query

dbo.usp_Update_ Pat_Visit_Amoun t @PatID

Now you can place the following code wherever you wish either on a command button to test it out and then maybe later on the afterupdate event of your AMNT textbox

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
With db
     Set qdf = .QueryDefs("qryPassthrough")
     qdf.SQL = "dbo.usp_Update_Pat_Visit_Amount '" & Me!txtPATID & "'"
     .Close
End With
'turn warnings off and run the passthrough
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryPassthrough", acNormal, acEdit
    DoCmd.SetWarnings True

Now the point of all of this is to show you that the stored procedure is actually updating that value for you and NOT the trigger so there really is not point keeping that trigger at all from how I understand this.

Bit long winded this post to say the least, but I hope I am helping you a little as to what is available to you :-)

[bherring]

Thnak you Jim for your work on this. Your explanation makes more sense to me.

Bypassing the trigger and creating an update procedure by itself,but the part I was missing was the Pass through Query. I have not worked with access in many years and neither has anyone else in our group. We use Microsoft SQL servers and SQL report writers and someASP programming. I was looking for the best way to handle it and my SQL guy said a trigger and since I have never worked with a trigger before that was forieng to me.

[Jim Doherty]

Thnak you Jim for your work on this. Your explanation makes more sense to me.

Bypassing the trigger and creating an update procedure by itself,but the part I was missing was the Pass through Query. I have not worked with access in many years and neither has anyone else in our group. We use Microsoft SQL servers and SQL report writers and someASP programming. I was looking for the best way to handle it and my SQL guy said a trigger and since I have never worked with a trigger before that was forieng to me.

Glad it helped you :-).

Regards