[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逐步讀取資料列的方法

[DB] 新增時如何指定自動流水號的值

在最近的案子中

因為客戶的懶惰，

雖然我們做了後台輸入畫面

但是客戶還是要求我們把A資料庫的東西自動帶到B資料庫

雖然客戶懶惰，但工程式更不勤勞

怎麼可能用我寫好的輸入介面一筆一筆新增XD

是我們的系統做的不好嗎?

不對!!!!!!絕對不是這樣的!!!!!

是我知道insert select這個指令!!!

insert into B.dbo.Employee (Id, Name)
select Id, Name
from A.dbo.Employee

這樣不就方便多了

但如果PK遇到自動增加的流水號

就會遇到下列錯誤訊息

Cannot insert explicit value for identity column in table 'Employee' when IDENTITY_INSERT is set to OFF.

意思是說：不能在 IDENTITY_INSERT OFF 的情況下新增 IDENTITY的欄位

反過來說，在 IDENTITY_INSERT ON 的情況下就可以新增 IDENTITY的欄位嗎?

沒錯!!  就是這樣

因此，如果要手動新增自動識別的值，必須像下面這樣

SET IDENTITY_INSERT B.dbo.Employee ON

insert into B.dbo.Employee (Id, Name)
select Id, Name
from A.dbo.Employee

SET IDENTITY_INSERT B.dbo.Employee OFF

不過這個有個缺點，就是不能使用 insert into ... select *

必須把要新增的欄位全部寫出來，其實還蠻麻煩的

至於在甚麼情況下需要這樣呢?

為什麼不讓B的Employee資料的主鍵自動新增呢?

因為有該死的Detail檔ㄚㄚ啊!!!

自動新增的話會找不到主檔的PK

但自動新增主鍵的情況下也不是完全沒解

只是比較麻煩，要先取得新增的流水號在insert到detail

所以....嗯哼

下回預告：新增後取得自動產生的流水號

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

從我第一份工作開始

公司的前輩就跟我說可以的話盡量不要用cursor

當時，我正在用strored procedure寫一份A3大小非常複雜的報表

似乎是遠傳的案子，小菜鳥第一支sp就複雜萬分

從此我對sp的印象就是：用程式很難做到的複雜事就交給stored procedure吧！！

後來證明這句話只對一半，用程式很難做到的不一定是複雜的事.....

資料庫效能也是一個大問題

沒錯！！就是效能，回到第一句話，前輩說用cursor會影響效能

可以的話盡量不要用cursor

還問前輩 "不用cursor的話為什麼要用stored procedure寫？"

可能是因為這位前輩只有大我一歲，所以才會這麼沒大沒小吧

最後在第一家公司還是沒有得到答案

後來進第二家公司之後，DBA還是說盡量不用使用cursor

雖然我忘記他講的原因是什麼了

但是他教了我一招，可以做到cursor做到的事並且不影響資料庫效能

用Temp Table取代Cursor

cursor通常用來逐筆處理資料使用，下面是一個簡單的範例（MS SQL Server 2008)

declare @myId int
declare @myName nvarchar(20)
declare @myCursor CURSOR

set @myCursor = CURSOR FAST_FORWARD
FOR
SELECT ID, NAME FROM Employee
open @myCursor
INTO @myId, @myName
WHILE @@FETCH_STATUS = 0
BEGIN

    --To Something

    FETCH NEXT FROM @myCursor
    INTO @myId, @myName

END

CLOSE @myCursor
DEALLOCATE @myCursor

用temp table來模擬cursor的操作模式，

簡單的說就是把SELECT ID, NAME FROM Employee的結果存到temp table中，在逐步讀取

所以必須先建立一個temp table如下

create table #tempEmployee
(
    ID int,
    NAME nvarchar(20)
)

然後把資料insert into select 到 #tempEmployee

insert into #tempEmployee (ID,NAME)
select ID,NAME
from Employee

接下來，就是逐步讀取這個temp table #tempEmployee

在MS SQL Server中使用SET ROWCOUNT 1來控制select時一次只撈出一筆資料

而且每做完一筆就刪掉，直到#tempEmployee沒有資料為止

declare @countTemp int --用來計算#tempEmployee還剩幾筆資料

--計算#tempEmployee資料數
select @countTemp = count(*) from #tempEmployee

while(@countTemp > 0)
begin
    set rowcount 1
    select @myId = ID, @myName = NAME from #tempEmployee
    --To Something
     
    --因為set rowcount 1的關係，所以一次只會刪一筆
    delete from #tempEmployee 

    --計算還剩幾筆，@countTemp > 0繼續
    select @countTemp = count(*) from #tempEmployee  
end

--#tempEmployee功成身退，你可以死了
drop table #tempEmployee

--把select的預設比數恢復正常
set rowcount 0

如此一般，完整的程式碼如下

declare @myId int
declare @myName nvarchar(20)

create table #tempEmployee
(
    ID int,
    NAME nvarchar(20)
)

declare @countTemp int --用來計算#tempEmployee還剩幾筆資料

--計算#tempEmployee資料數
select @countTemp = count(*) from #tempEmployee

while(@countTemp > 0)
begin
    set rowcount 1
    select @myId = ID, @myName = NAME from #tempEmployee
    --To Something
     
    --因為set rowcount 1的關係，所以一次只會刪一筆
    delete from #tempEmployee 

    --計算還剩幾筆，@countTemp > 0繼續
    select @countTemp = count(*) from #tempEmployee  
end

--#tempEmployee功成身退，你可以死了
drop table #tempEmployee