Audit Trail in SProc
-
I have done little SP programming and have a request to create an audit trail based on one type of record returned from a query inside of a proc.
CREATE PROCEDURE dbo.FetchCaseComment
@IVDNO [int]
,@EmpNo char(4) = NULL
AS
SET NOCOUNT ON
SET DATEFORMAT mdy
DECLARE @ThruDate char(12) -- Floating Date Variable
SET @ThruDate = (SELECT ThruDate FROM SEMS.dbo.ArchiveControl WHERE AreaName = 'SECC')
SELECT CC_IVDnbr, CC_Type, CC_Date, CC_EmpNo, CC_Cmnt
FROM SEMS.dbo.SECC
WHERE CC_IVDnbr = @IVDNO AND CC_Date < @ThruDate
ORDER BY CC_Date DESC, CC_Seq DESC
GO
The request is based on a return of CC_Type = 50. If one comes back, I need to insert a record into another database. I do not know how to accomplish this. Do I have to create a ## table and put the result set in then query the ## table for the type or do I use a Cursor ( of which I have never used, yet!)?
If anybody has done something similar
, could I see a sample or just point me to an article if possible.TIA
Bill.
-
Do you want to write the record only if a record CC_Type = 50 appears in the result of the stored procedure or do you want the record written if it exists in the table?
If you want the extra record written if it exists in the table, a trigger would be the thing to use. If you want to write the record only if it shows up in the stored procedure result that gets a little more complicated depending on whether you can have more than one row and if you have more than one row in the SP, do you want to write multiple audit records, or do you want to just write one audit record if there are any records that meet that qualification.
The answer to your question will vary depending on the answers to mine.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
In this one nothing is easy. May have more than one row and write one audit per record found. So initial request may return 90 records with 3 of type 50. That would require 3 records written to the Audit table.
Thx!
-
Actually that might make it real simple. But before I can tell I need to ask a few more questions:
Do you want the audit records to be written only when the stored procedure is executed? And if so, do you want the audit records written again if the stored procedure is run again? Or do you just want to record the fact that those records exist whether or not a stored procedure is executed?
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
IRS stuff ya know. Every record (Type 50) every time for all time. Every time the proc runs and every record that matches the type. Does that answer your question well enough?
Bill
-
It doesn't make much sense to me, but I'm not familiar with your system and its requirements... Based on what you told me I would just throw in a simple INSERT ...SELECT statement into your procedure, something like this:
INSERT DBName.dbo.AuditTableName (column1, column2, etc...) SELECT column1, column2, etc... FROM SEMS.dbo.SECC WHERE CC_Type = 50
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Thx for your input.
These records are historical comments posted to a clients case. Every time someone makes a request for a comment, we have to write an audit of Who, When, Which Type, and Where they requested it from.
Example: A request to view comments from case #222 comes through and there are 300 separate comments (records) and 25 of those 300 are type 50, we need to write an audit for each of the 25 comments. An hour later the same person makes another request for the same cases comments (the same 300 returned), we need to write another audit for each of the 25 one more time. The only difference between the two requests would be the time the request was made. All of the other information would probably be the same unless the requestor moved to another machine to initiate the query.
So every time a record of type 50 passes through the SP a new audit record has to be written.
Thanks again for your time dcpeterson.
-
Am I missing something or wouldn't the INSERT recommended get every type 50 record in the table SECC and not just the type 50 records returned by the stored procedure?
Should it be
INSERT DBName.dbo.AuditTableName (column1, column2, etc...)
SELECT column1, column2, etc... FROM SEMS.dbo.SECC
WHERE CC_Type = 50 and CC_IVDnbr = @IVDNO AND CC_Date < @ThruDate
Steve
-
That's a good point, I was basically assuming that all the other restriction clauses used in generating the SP's result set would be included in the INSERT, but I didn't explicitly say so. I was primarily concerned with providing the general mechanism that could be used to accomplish the goal, rather than providing all the specifics.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply