record of updates

Question

Post reply

record of updates

newbie_sql

SSC Eights!

Points: 954
More actions
December 4, 2009 at 8:49 am

#214548

I am running the following 2 sql's
UPDATE dbo.adds SET code = dbo.format_chars(code)
UPDATE dbo.adds SET code = upper(substring(pcode, 1, len(code) - 3)
how do I write a sql to get a record of the 'id' that the above sql's have impacted-basically write an audit table to reflect the id's updated & the date on which they were updated.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 736494

December 4, 2009 at 9:25 am

#1087836

those statements will update EVERY record.

NickDBA Hall of Fame Points: 3792 More actions · Answer 2

Yes, there's no [WHERE] clause?

To audit you'll need to set up an UPDATE trigger on the table in question. Check BOL for INSERTED / DELETED tables inside triggers

newbie_sql SSC Eights! Points: 954 More actions · Answer 3

where clause is: where pcode like '%[^a-zA-Z0-9_]%'

another problem is:first time I Want to run the 2 queries for the entire database & See what 'ids' have been updated but next time I only want to run for records not updated last time

how to create a trigger on the table to reflect these updates?

Lowell SSC Guru Points: 323518 More actions · Answer 4

as others have pointed out, without a WHERE clause, you are updating every row.

here's an example, which uses the output clause, so you can see how to use it:

Create Table adds(adid int identity(1,1) not null primary key, code varchar(30) )

insert into adds(code)

SELECT 'aliceblue' UNION ALL

SELECT 'antiquewhite' UNION ALL

SELECT 'aqua*' UNION ALL

SELECT 'aquamarine' UNION ALL

SELECT 'azure' UNION ALL

SELECT 'beige' UNION ALL

SELECT 'bisque' UNION ALL

SELECT 'black*' UNION ALL

SELECT 'blanchedalmond' UNION ALL

SELECT 'blue*' UNION ALL

SELECT 'blueviolet' UNION ALL

SELECT 'brown' UNION ALL

SELECT 'burlywood' UNION ALL

SELECT 'cadetblue'

declare @MyResults TABLE(ID int,newcode varchar(30),oldcode varchar(30) )

UPDATE dbo.adds

SET code = upper(substring(code, 1, len(code) - 3) )

OUTPUT

INSERTED.adid,

INSERTED.code,

DELETED.code

INTO @MyResults

WHERE LEFT(code,1) = 'a'

select * from @MyResults

--results

ID newcode oldcode

1 ALICEB aliceblue

2 ANTIQUEWH antiquewhite

3 AQ aqua*

4 AQ aqua*

5 AQUAMAR aquamarine

6 AZ azure

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

newbie_sql SSC Eights! Points: 954 More actions · Answer 5

Thanks-but couldnt get the output clause to work.

I have thought of creating a trigger to insert a row into an log_table for each update on the user_table

CREATE TRIGGER [log_table] ON [dbo].[user_table]

FOR UPDATE AS BEGIN

SET NOCOUNT ON

INSERT INTO [dbo].[log_table]

(

h_type ,

h_date ,

rec_count)

SELECT 'formatting', getdate(),<??select count(*) from ??>

FROM updated

END

But I do not understand how to get the count of the updated rows to put into the trigger!! 🙁

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 6

Are you putting the rowcount in all rows you're inserted into the audit table? the count is easy

create trigger ...

for update

....

declare @cnt

select @cnt = count(*) from inserted

newbie_sql SSC Eights! Points: 954 More actions · Answer 7

thanks-this helps

Another quick question for OUTPUT clause:

I created the auit table as below

tbl_audit

(

rec_id identity

type varchar

date datetime

old_num varchar

new_num varchar

own_id uniqueidentifier

)

Running the following code:

UPDATE dbo.adds SET code = dbo.format_chars(code)

OUTPUT 'format',getdate(),deleted.pnum,inserted.pnum,own_id into tbl_audit

WHERE pnum like '%[^0-9]%'

own_id is a field in dbo.adds but still when I try to use it with the OUTPUT field it gives me an error:

Invalid column name 'own_id'

Can I not use this field as a part of OUTPUT in the tbl_audit

newbie_sql SSC Eights! Points: 954 More actions · Answer 8

any clues on my output clause query-i still cant get the correct sql