Archive Stored Procedure

My SP looks like this that originally populates the record set.

CREATE PROCEDURE dbo.sp_insert_file

@.KBID int,
@.rev int,
@.filename nvarchar(50),
@.moddate datetime,
@.owner nvarchar(50),
@.author nvarchar(50)

As

INSERT INTO KBFile
(KBID, rev, filename, moddate, owner, author)
VALUES (@.KBID,@.rev,@.filename,@.moddate,@.owner, @.author)
GO

I need to have a stored procedure that will select a recordset based on KBID(PK) and archive the entire record set to another table. I then need to be able to UPDATE the individual records for this record set in the KBFile table.

I am trying to create an 'audit' trail while maintaining previous records.

Can someone point me in the right direction?

What about using an update trigger ? You will be able to audit all change events (not only these from stored procedures)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Often, a TRIGGER is used for Audit trails since the TRIGGER execution cannot be avoided. Audit trails are more 'risky' when stored procedures are used.

You might find one of these references pointing to the use of TRIGGERS to be helpful:

Trigger -Audit Trail
http://www.nigelrivett.net/AuditTrailTrigger.html
http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart1.asp
http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart2.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp

sql

Archive Stored Procedure

My SP looks like this that originally populates the record set.

CREATE PROCEDURE dbo.sp_insert_file

@.KBID int,
@.rev int,
@.filename nvarchar(50),
@.moddate datetime,
@.owner nvarchar(50),
@.author nvarchar(50)

As

INSERT INTO KBFile
(KBID, rev, filename, moddate, owner, author)
VALUES (@.KBID,@.rev,@.filename,@.moddate,@.owner, @.author)
GO

I need to have a stored procedure that will select a recordset based on KBID(PK) and archive the entire record set to another table. I then need to be able to UPDATE the individual records for this record set in the KBFile table.

I am trying to create an 'audit' trail while maintaining previous records.

Can someone point me in the right direction?

What about using an update trigger ? You will be able to audit all change events (not only these from stored procedures)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Often, a TRIGGER is used for Audit trails since the TRIGGER execution cannot be avoided. Audit trails are more 'risky' when stored procedures are used.

You might find one of these references pointing to the use of TRIGGERS to be helpful:

Trigger -Audit Trail
http://www.nigelrivett.net/AuditTrailTrigger.html
http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart1.asp
http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart2.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp

Append a field

Hello,

How do I append data on an update?
I have a table with a field that is nVarchar(1000) and the initial insert is a few sentences. If I wanted to add to that row using an update statement and without starting at the end of the sentences, how would I write that?

Update table set fieldname = 'more data' where value = @.variable

instead of

Update table set fieldname = 'initial data more data' where value = @.variable

and the 'more data' appends to the initial data... hmmm

help please.I'm not exactly following you. If you want to do both an INSERT and an UPDATE you need 2 different commands.

INSERT INTO table (ID, fieldname) VALUES(@.MyID, 'initial data')
UPDATE table SET fieldname = fieldname + ' more data' WHERE ID = @.MyID

Maybe you could explain further?

Terri|||OK, lets see...

An existing field containing data:

'A small red dog crapped on my lawn.'

In a textbox append to that same field:

'Then the owner cleaned it up.'

So now the field will contain:
'A small red dog crapped on my lawn. Then the owner cleaned it up.'

If i perform the statement like this:
Inset Into table (fieldname) Values ('Then the owner cleaned it up.') where value = @.value

Won't the 'A small red dog crapped on my lawn.' part be overwritten?|||No, you'd get an error ;-) You can't put a WHERE on an INSERT. What you need is an UPDATE:

UPDATE table SET fieldname = RTRIM(fieldname) + 'Then the owner cleaned it up.' WHERE value = @.value

(Note that you'd only need the RTRIM if the datatype of that field is char. Varchar data will not have extra spaces padding it out to the field length.

Terri