updatetrigger for logging and update stored proc conflict

[LTusing]

I have an update trigger for each column in my tables that log when items are inserted, updated, and deleteted. I am also calling a stored procedure to update the tables and there seems to be a conflict. Both programs work when isolated but not one with the other. Does any one know how I may reconcile this problem? Thank You!

 

[SQLBill]

How do you know there is a conflict? Do you get error messages?

You don't give us enough information to give you any kind of help as we don't know what the problem is.

-SQLBill

Posting advice: FAQ481-4875

 

[LTusing]

Yes, when calling the update stored procedure with the triggers in place, it responds unable to connect to 'mydatabase' OR it will state error 'invalid item' and will not edit data. However, if I remove the Update trigger, the command to call the update stored procedure will edit data without problems.

 

[SQLSister]

Likely there is a bug in the trigger which only appears when that particular stored proc is executed. Possibly it istrying to connect to a database or server which it cannot find or the sp is handling a multiple reocd insert, updtae or delete and the trigger is wrritten so that it only handles one record at a time. Or lots of other possibilities including the sp is violating the business rules of the rtrigger.

To help further we'd need to see the trigger and the proc.

Questions about posting. See faq183-874

 

[LTusing]

Thank you for taking time to look at my code:

This VB code calls the stored procedure to update data.
Private Sub Update_Record_Click()

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rsConsent As New ADODB.Recordset
Dim param1 As ADODB.Parameter
Dim param2 As ADODB.Parameter
Dim param3 As ADODB.Parameter
Dim param4 As ADODB.Parameter
Dim param5 As ADODB.Parameter
Dim param6 As ADODB.Parameter
Dim param7 As ADODB.Parameter

cnn.Provider = "sqloledb"
cnn.Properties("Data Source").Value = PEDSBOT
cnn.Properties("Initial Catalog").Value = "fas.adhd.blind"
cnn.Properties("Integrated Security").Value = SSPI
cnn.Open

'Connect
cmd.ActiveConnection = cn
cmd.CommandText = "dbo.procUpdate_Consent_1"
cmd.CommandType = adCmdStoredProc

Set param1 = cmd.CreateParameter(, adInteger, adParamInput)
cmd.Parameters.Append param1
param1.Value = icd_id
Set param2 = cmd.CreateParameter(, adInteger, adParamInput)
cmd.Parameters.Append param2
param2.Value = Me.icd_id
Set param3 = cmd.CreateParameter(, adInteger, adParamInput)
cmd.Parameters.Append param3
param3.Value = Me.pt_id
Set param4 = cmd.CreateParameter(, smalldatetime, adParamInput)
cmd.Parameters.Append param4
param4.Value = Me.vdt_con
Set param5 = cmd.CreateParameter(, smalldatetime, adParamInput)
cmd.Parameters.Append param5
param5.Value = Me.cons_dt
Set param6 = cmd.CreateParameter(, smalldatetime, adParamInput)
cmd.Parameters.Append param6
param6.Value = Me.vdt_hip1
Set param7 = cmd.CreateParameter(, smalldatetime, adParamInput)
cmd.Parameters.Append param7
param7.Value = Me.hipa_dt

Set rs = cmd.Execute
Debug.Print rs(0)
cnn.Close
'Close the Connection and Clean Up
rsConsent.Close
Set rsConsent = Nothing
cnn.Close
Set cnn = Nothing
cnn.Close

End Sub

This trigger logs all updates to the table:
CREATE TRIGGER [UpdateConsent_trigger] ON [dbo].[Consent]
FOR UPDATE
AS
BEGIN

DECLARE @icd_id integer
DECLARE @dpt_id integer
DECLARE @ipt_id integer
DECLARE @dvdt_con smalldatetime
DECLARE @ivdt_con smalldatetime
DECLARE @dcons_dt smalldatetime
DECLARE @icons_dt smalldatetime
DECLARE @dvdt_hip smalldatetime
DECLARE @ivdt_hip smalldatetime
DECLARE @dhipa_dt smalldatetime
DECLARE @ihipa_dt smalldatetime
DECLARE @guid uniqueidentifier
SET @guid=NEWID()
INSERT ITEM VALUES (@GUID, 'Yak Hoof')
DECLARE UpdateLOGTABLE_Consent CURSOR
FOR SELECT d.icd_id, d.pt_id, i.pt_id, d.vdt_con, i.vdt_con, d.cons_dt, i.cons_dt, d.vdt_hip, i.vdt_hip, d.hipa_dt, i.hipa_dt
FROM deleted d, inserted i
WHERE d.icd_id=i.icd_id
OPEN UpdateLOGTABLE_Consent
FETCH NEXT FROM UpdateLOGTABLE_Consent
INTO @icd_id, @dpt_id, @ipt_id, @dvdt_con, @ivdt_con, @dcons_dt, @icons_dt, @dvdt_hip, @ivdt_hip, @dhipa_dt, @ihipa_dt
WHILE (@@FETCH_STATUS=0) BEGIN
SELECT @guid=NEWID()
INSERT INTO LOGTABLE_Consent
(icd_id, pt_id, vdt_con, cons_dt, vdt_hip, hipa_dt, type, event_id, user_id, event_time)
VALUES (@icd_id, @dpt_id, @dvdt_con, @dcons_dt, @dvdt_hip, @dhipa_dt, 'E', @guid,
CURRENT_USER, CURRENT_TIMESTAMP)
INSERT INTO LOGTABLE_Consent
(icd_id, pt_id, vdt_con, cons_dt, vdt_hip, hipa_dt, type, event_id, user_id, event_time)
VALUES (@icd_id, @ipt_id, @ivdt_con, @icons_dt, @ivdt_hip, @ihipa_dt, 'N', @guid,
CURRENT_USER, CURRENT_TIMESTAMP)
FETCH NEXT FROM UpdateLOGTABLE_Consent
INTO @icd_id, @dpt_id, @ipt_id, @dvdt_con, @ivdt_con, @dcons_dt, @icons_dt, @dvdt_hip, @ivdt_hip, @dhipa_dt, @ihipa_dt
END
CLOSE UpdateLOGTABLE_Consent
DEALLOCATE UpdateLOGTABLE_Consent
END

This procedure updates the data.
CREATE PROCEDURE [update_Consent_1]
(@icd_id_1 [int],
@icd_id_2 [int],
@pt_id_3 [int],
@vdt_con_4 [smalldatetime],
@cons_dt_5 [smalldatetime],
@vdt_hip_6 [smalldatetime],
@hipa_dt_7 [smalldatetime])

AS UPDATE [fas.adhd.blindSQL].[dbo].[Consent]

SET [icd_id] = @icd_id_2,
[pt_id] = @pt_id_3,
[vdt_con] = @vdt_con_4,
[cons_dt] = @cons_dt_5,
[vdt_hip] = @vdt_hip_6,
[hipa_dt] = @hipa_dt_7

WHERE
( [icd_id] = @icd_id_1)
GO

THANK YOU!