你是一个在线回扣公司的数据库开发者。客户拥有的存货价格在SQL Server 200

11 查阅

你是一个在线回扣公司的数据库开发者。客户拥有的存货价格在SQL Server 2005/2008数据库中。为了对存货价格历史记录做跟踪所有存货价格被记录的更新。为了纠正关于价格更新，每天发生的错误必须也被记录。当错误被记录时，标识存储产生错误的消息必须被返回到客户应用程序。你必须确定合适的条件被记录和合适的的消息被生成。你应该用哪个过程？

CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimalAS BEGINDECLARE @Msg varchar(50)UPDATE Stocks SET CurrentPrice = @PriceWHERE StockID = @ StockIDAND CurrentPrice <> @ PriceIF @@ERROR <> 0RAISERROR (‘Error %d occurred updating Stock %d.’, 10, 1, @@ERROR, @StockID) WITHLOGIF @@ROWCOUNT > 0BEGINSELECT @Msg = ‘Stock’ + STR (@StockID) + ‘updated to’ + STR (@Price) + ‘.’EXEC master. . xp_LOGEVENT 50001, @MsgENDEND

CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimalAS BEGINUPDATE Stocks SET CurrentPrice = @PriceWHERE StockID = @ StockIDAND CurrentPrice <> @ PriceIF @@ERROR <> 0PRINT ‘ERROR’ + STR(@@ERROR) + ‘occurred updating Stock’ +STR (@StockID)+ ‘.’IF @@ROWCOUNT > 0PRINT ‘Stock’ + STR (@StockID) + ‘updated to’ + STR (@Price) + ‘.’END

CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimalAS BEGINDECLARE @Err int, @RCount int, @Msg varchar(50)UPDATE Stocks SET CurrentPrice = @PriceWHERE StockID = @ StockIDAND CurrentPrice <> @ PriceSELECT @Err = @@ERROR, @RCount = @@ROWCOUNTIF @Err <> 0BEGINSELECT @Msg = ‘Error’ + STR(@Err) + ‘occurred updating Stock’ + STR (@StockID) + ‘.’EXEC master..xp_logevent 50001, @MsgENDIF @RCOUNT > 0BEGINSELECT @Msg = ‘Stock’ + STR (@StockID) + ‘updated to’ + STR (@Price) + ‘.’EXEC master. . xp_LOGEVENT 50001, @MsgENDEND

CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimal AS BEGINDECLARE @Err int, @RCount int, @Msg varchar (50)UPDATE Stocks SET CurrentPrice = @PriceWHERE StockID = @StockIDAND CurrentPrice <> @PriceSELECT @Err = @@ERROR, @RCount = @@ROWCOUNTIf @Err <> 0RAISEERROR (‘Error %d occurred updating Stock %d.’, 10, 1, @Err, @StockID) WITH LOGIf @RCount > 0BEGINSELECT @Msg = ‘Stock’ + STR (@StockID) + ‘update to’ + STR (@Price) + ‘.’EXEC master. . xp_logevent 50001, @MsgENDEND

参考答案:

SQL试