TSQL--消失的欄位。子查詢的神奇茶包

莊創偉
2 min readAug 27, 2018

某天同事回報某資料表的資料被全部刪除了。經過一番追查確定是子查詢的問題。

兩個資料表的架構大致如下

Table A1               Table A2
--------------- ---------------
A1ID A2ID
... A1ID
..... ......

A2資料表為A1資料表的明細資料,彼此之間以A1ID這個欄位關聯。
當A1的資料被刪除時,要連動刪除A2的資料。

很簡單的需求,在A1資料表中加個trigger 一行指令就可以輕鬆搞定

delete A2 form A1ID =(select A1ID from deleted)

本來一直頭好壯壯的運行,直到某天資料庫異動,上面希望統一主Key名稱為ID,於是新的資料架構如下。

Table A1               Table A2
--------------- ---------------
ID ID
... A1ID
..... ......

A2.A1ID對應到A1.ID的欄位

此時照道理原先的trigger應該要失效並處發錯誤才對,因為A1資料表中已經沒有A1ID這個欄位了。

But~~他不但活得好好的還把資料全給刪了,傑克這真是太靠北了。

稍微追查一下原因,主要是因為A2表中有同名的A1ID 當 再子查詢中查無該欄位時,會自動帶入主表的欄位

來看看MSDN怎麼說的(還用重要標起來,看來踩雷的應該不只我一個@@)

重要如果資料行被不存在於由子查詢的 FROM 子句所參考之資料表中的子查詢所參考,但存在於由外部查詢之 FROM 子句所參考的資料表中,則查詢會在沒有錯誤之下執行。 [SQL Server] 會以外部查詢中的資料表名稱,對子查詢中的資料行進行隱含限定。

所以該行trigger 就轉譯成 delete A2 form A1ID =(select A1ID from A2)

然後資料就刪光光了~~~

參考資料:MSDN

--

--

莊創偉

學海無涯。但是為了生計還是得下海的風塵男子