• I will loop through all the controls on my website and dynamically generate an INSERT INTO statement and place the results into a temporary table (is there a way I can truely make this a temporary table via ADO.Net code or do I have to use presistent "temp" table?)


• Run a query and place all values that will be updated into another temp table


• Run a query and place all values that will be inserted into the same temp table as those being updated (I will use a flag to determine if it is an update or insert)


• Run the update query


• Run the insert query


• Use a cursor to loop through that second temp able and one by one insert rows into the log table

quote:

---

Originally posted by Billkamm

Well there is additional data I need to put in the audit table such as the user perfroming the action and I don't know of a way to "pass" information to a trigger

---

-- Will update or insert document information into tblApplicationDocuments
CREATE PROCEDURE dbo.usp_UpdateAppDocs
(
	-- The user ID is required, so it can be added to the usage log
	@pkUserID			int,

	-- The application number
	@AppNumber			int,
	
	-- The date and time the status changed
	@DateStatusChanged	datetime
)
AS

SET NOCOUNT ON

-- Log the updated documents
INSERT INTO tblUsageLog
(
	fkApplicationNumber,
	DateTimeOfAction,
	fkUserID,
	Description
)
SELECT
	@AppNumber,
	@DateStatusChanged,
	@pkUserID,
	'Changed ' + logInfo.DocumentName + ' from ' + logInfo.OldDocumentStatus + ' to ' + NewDocumentStatus
FROM
(
	SELECT 	
		DocumentName,
		DocumentStatus OldDocumentStatus,
		(
			SELECT DocumentStatus
			FROM tlkpDocumentStatuses
			WHERE pkDocumentStatusID = S.fkDocOrTaskStatus
		) NewDocumentStatus
	-- Alias D is for "Destination", Alias S is for "Source"
	FROM qryApplicationDocuments AS D JOIN #temptblApplicationDocuments AS S
		ON D.fkApplicationNumber = S.fkIdNumber
		AND D.fkDocumentID = S.fkDocOrTaskID
	WHERE
	(
		(D.fkDocumentStatus <> S.fkDocOrTaskStatus
		 OR (D.fkDocumentStatus IS NULL AND S.fkDocOrTaskStatus IS NOT NULL)
		 OR (D.fkDocumentStatus IS NOT NULL AND S.fkDocOrTaskStatus IS NULL))
		AND fkApplicationNumber = @AppNumber
	)
) logInfo

CREATE TABLE [dbo].[tblApplicationDocuments] (
	[pkApplicationDocumentID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[fkApplicationNumber] [int] NOT NULL ,
	[fkDocumentID] [int] NOT NULL ,
	[DateReceived] [datetime] NULL ,
	[fkDocumentStatus] [int] NULL 
) ON [PRIMARY]

CREATE TABLE [dbo].[tblUsageLog] (
	[pkUsageLogID] [int] IDENTITY (1, 1) NOT NULL ,
	[fkApplicationNumber] [int] NULL ,
	[fkBrokerID] [int] NULL ,
	[DateTimeOfAction] [datetime] NOT NULL ,
	[fkUserID] [int] NOT NULL ,
	[Description] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]