[SP] 在有trigger的Table下,無法執行insert select指令?

新案子我寫了很多stored produce, view, trigger

也用了很多insert into select ... 這種語法

但是只要insert到有trigger的Table

就會遇到下列錯誤訊息

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Subquery出來的資料超過一筆而造成錯誤

之前在google上找了很久

本來以為有逐筆執行的方法

一直想從AFTER下手

ALTER TRIGGER [dbo].[ASSIGEROLE]
    [dbo].[Dat_Visit2Staff]
AFTER UPDATE,INSERT
AS
BEGIN

但始終未果

於是每次要insert into select之前，我就先把trigger關掉，insert完再打開

disable trigger tr_addTel on Tel

insert into Tel (Id, Name, Tel)
select Id, Tel
from Employee

enablet rigger tr_addTel on Tel

但這樣子其實就失去trigger的意義了，

insert into select的資料沒有做到trigger裡的動作

可能會造成資料不正確

用了這個方法先暫時解決了問題，

過了幾了禮拜後其他資料表也遇到一樣的問題

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

有時候解BUG真的要天時地利人合

靈感沒來時，怎麼加班都是浪費時間

這次我又仔細看了一下錯誤訊息，

一樣是Subquery出來的資料超過一筆而造成錯誤

原來我的trigger中有這樣子的參數指定

DECLARE @staffType varchar(3)
SET @staffType = (SELECT STAFF_TYPE from INSERTED )

但是當insert into select時

SELECT STAFF_TYPE from INSERTED 就不只一筆資料

多筆資料要塞到一個 varchar 中難怪會出錯

所以需要逐筆讀取資料!!!

沒錯!!  逐筆讀取就是CURSOR

把INSERTD塞到CURSOR後逐筆讀取就搞定了

範例如下

DECLARE @staffType varchar(3)
DECLARE @staffId nvarchar(128)

DECLARE @MyCursor CURSOR 
DECLARE @SQLCommand nvarchar(200)
	
SET @MyCursor = CURSOR FAST_FORWARD 
FOR 
SELECT STAFF_ID,STAFF_TYPE from INSERTED
OPEN @MyCursor 
FETCH NEXT FROM @MyCursor 
INTO @staffId,@staffType
WHILE @@FETCH_STATUS = 0 
BEGIN
     --do something
     FETCH NEXT FROM @MyCursor 
     INTO @staffId,@staffType
END 
CLOSE @MyCursor 
DEALLOCATE @MyCursor 

看吧!!!CURSOR真好用

但眾所皆知，CURSOR可能會影響資料庫校能

有其他辦法可以不用CURSOR就能達到逐筆讀取的目的嗎?

當然有，請參考之前文章

[程式] stored procedure中不使用cursor逐步讀取資料列的方法