Select Statement in Trigger Returns data to client 1

[jbroyles]

I am trying to update a log table using an After Insert trigger. I can update the table with no problem, but it requires a select statement in the Trigger which attempts to return the data to the client. I am using data access pages for the client and they do no accept the returning data. I just want to update the log table and not return data. trigger sample is below.

ALTER TRIGGER [Log]
ON dbo.Prospects
FOR INSERT
AS
SET NOCOUNT ON
DECLARE @ID int, @Status int, @user nvarchar(50)
SELECT @ID=(SELECT ProspectID from Inserted),@Status=(SELECT ProspectStatus FROM Inserted),@user=(SELECT EnteredBy from Inserted)
INSERT INTO dbo.EventLog (ProspectID,ProspectStatus,UpdatedBy)
VALUES (@ID, @Status, @user)

Can anybody tell me how to stop SQL from performing the update without sending the recordset back to the client?

 

[tlbroadbent]

Simplify the trigger like this.

ALTER TRIGGER [Log]
ON dbo.Prospects
FOR INSERT
AS
SET NOCOUNT ON

INSERT INTO dbo.EventLog (ProspectID, ProspectStatus, UpdatedBy)
SELECT ProspectId, ProspectStatus, EnteredBy
FROM Inserted Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.

 

[jbroyles]

Thanks Terry,

But I think I'm looking at the wrong problem. I tried your syntax and still recieved the same error. Then I completely eliminated the select statement and still got the error. The test syntax I used without the select statement was:

ALTER TRIGGER [Log]
ON dbo.Prospects
FOR INSERT
AS
SET NOCOUNT ON

INSERT INTO dbo.EventLog (ProspectID, ProspectStatus, UpdatedBy)
VALUES (1,1,'TEST')

The error message reads: "The data was added or updated in the database, but the data won’t be displayed because it doesn’t satisfy the criteria in the underlying record source"

The trigger works and updates dbo.EventLog but raises that error on the client. I have a hyperlink on the DAP that saves the current record (causing the trigger to fire)and is supposed to advance to the next DAP. When the error is raised the save operation works but the navigation fails due to the error.

Help!!!

 

[jbroyles]

Update into direction I'm heading.

I think I figured out where my problem lies. I didn't set the ResyncCommand on the Data Access Page. As soon as I put a Statement like
Select * FROM ProspectsView
(where ProspectsView is the recordset that feeds the DAP)into the ResyncCommand Property the problem vanished even with the Select Statement included.

Now, I just need to figure out how to use the ResyncCommand property.