在trigger中利用@@rowcount

作者：
最近在對於系統作些調整、修改。其間，為了資料的一致性，最後還是決定採用觸發（trigger） 。為了照顧好資料庫（的效能），於是在思考如何減少trigger內不必要的語法執行（也就是沒資料被異動時，就算trigge被呼叫起來也不需要作任何動作）

翻了一下手上的工具書-Inside Microsoft SQL Server 2005：T-SQL PROGRAMMING 中文版。在這本書（個人覺得這本書非常充實，欲瞭解MS SQL 2005、新功能、調教資料庫與進階應用等，建議可以看看本書）中提到一個作法，利用@@rowcount來協助判斷。

也就是在trigger一開始，就直接利用@@rowcount來判斷使否有資料被異動（以我的案例，我是要針對是否有資料真的被刪除，才去作一些處理）。有資料被異動，也就是@@rowcount傳回大於0的值，方才執行後續的SQL語法。否則就直接跳出，避免多作任何SQL語法，造成無用的資源浪費。

不過，一開始嘗試時卻是失敗的…後來才注意到，因為我是利用SQL 2005 Studio 產生的trigger範例語法，因此他內建會放入SET NOCOUNT ON; ，這會導致@@rowcount不傳回值。

可是，在MS SQL官方文件中明明提及 - 即使 SET NOCOUNT 是 ON，也會更新 @@ROWCOUNT 函數。但…trigger中使用@@rowcount，就是會被影響！

以下，就是最基本的處理範例

  1. CREATE TRIGGER [dbo].[tri_test]
  2. ON [dbo].[table_test]
  3. AFTER DELETE
  4. AS
  5. BEGIN
  6. -- SET NOCOUNT ON added to prevent extra result sets from
  7. -- interfering with SELECT statements.
  8. --SET NOCOUNT ON;
  9. --以上三行語法為 SQL 2005 內建會產生的

  11. IF @@rowcount=0
  12. BEGIN
  13. --直接離開
  14. RETURN;
  15. END
  16. ELSE
  17. BEGIN
  18. --要作的處理
  19. UPDATE table_otherone SET status='E'
  20. FROM table_otherone WITH(nolock) INNER JOIN deleted WITH(nolock) ON (table_otherone.id = deleted.id);
  21. END
  22. END


附註：
就自己的經驗，在SP內如果執行SET NOCOUNT ON; ，居然會導致PHP的程式無法接到在SP內，使用select的傳回值。懷疑PHP的判斷方式，是先看是否有資料？才決定要不要接資料？

留言

張貼留言