SQL Server Performance

logging when row gets updated

Discussion in 'General Developer Questions' started by v1rtu0s1ty, Jun 16, 2004.

  1. v1rtu0s1ty New Member

    Hey guys,<br /><br />I wanted to log a message say "row updated" everytime a row gets modified successfully. I have a script below for our example:<br /><br />Can I do this instead, just don't know if it's a good idea:<br /><br />I will create a temporary table that will get populated in every loop which will have a default value of NULL and the id number that I need to modify. Or probably, you might have a better idea of logging. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Please see my comments to on some few lines below.<br /><br />-----------script-------------<br />DECLARE @RowCount INT, @Error INT, @loop INT<br />DECLARE @Total INT<br />DECLARE @TempName as VARCHAR(10)<br /><br />select @Total = count(*) from author2<br /><br />set @loop = 1<br />set rowcount 1<br /><br />WHILE @loop &lt;= @Total<br />BEGIN<br /><br />SELECT @TempName = [name]<br />FROM author2<br />WHERE id = @loop<br /><br />BEGIN TRAN<br />UPDATE author1<br />SET author1.lastname=author2.lastname<br />FROM author2<br />INNER join author1<br />ON author1.name=author2.name <br />and author1.name=@TempName<br /><br />SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT <br /><br />IF @Error &lt;&gt; 0<br />BEGIN<br />PRINT 'Something went wrong'<br />-- the line below is what am planning to replace<br />-- with an INSERT STATEMENT to the temporary table<br />PRINT 'ROLLING BACK...' <br />ROLLBACK<br />END<br />ELSE<br />IF @Error = 0<br />BEGIN<br />-- the line below is what am planning to replace<br />-- with an INSERT STATEMENT to the temporary table<br />PRINT 'Update is successful...committing'<br />COMMIT<br />END<br /><br />IF @RowCount = 0 <br />PRINT 'No rows updated.'<br />ELSE<br />PRINT STR(@RowCount) + ' rows updated.'<br /><br />SET @loop = @loop + 1<br />END <br /><br />select * from author1<br />select * from author2<br />

Share This Page