2009/10/16

在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 內建會產生的

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


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

沒有留言:

Related Posts Plugin for WordPress, Blogger...