你真的了解觸發(fā)器么 數(shù)據(jù)實(shí)時(shí)同步更新問(wèn)題剖析
更新時(shí)間：2013年01月17日 12:19:02 作者：
觸發(fā)器就是我們想要的神器了。我們可以在那張動(dòng)態(tài)表上新建觸發(fā)器。觸發(fā)器的實(shí)質(zhì)就是個(gè)存儲(chǔ)過(guò)程，只不過(guò)他調(diào)用的時(shí)間是根據(jù)所建的動(dòng)態(tài)表發(fā)生該表而執(zhí)行(即：Insert新數(shù)據(jù)，Update或者Delete數(shù)據(jù))具體怎么使用觸發(fā)器，今天我這里就不介紹了
當(dāng)我們想更新一張動(dòng)態(tài)表的時(shí)候(即：表中的數(shù)據(jù)不斷的添加)，也許我們會(huì)用數(shù)據(jù)庫(kù)代理，通過(guò)寫作業(yè)，然后讓他定時(shí)查詢動(dòng)態(tài)表中最新添加的數(shù)據(jù)，然后更新數(shù)據(jù)。這樣時(shí)能實(shí)現(xiàn)更新數(shù)據(jù)的要求，但是數(shù)據(jù)卻不能實(shí)時(shí)同步更新。
這個(gè)時(shí)候，觸發(fā)器就是我們想要的神器了。我們可以在那張動(dòng)態(tài)表上新建觸發(fā)器。觸發(fā)器的實(shí)質(zhì)就是個(gè)存儲(chǔ)過(guò)程，只不過(guò)他調(diào)用的時(shí)間是根據(jù)所建的動(dòng)態(tài)表發(fā)生該表而執(zhí)行(即：Insert新數(shù)據(jù)，Update或者Delete數(shù)據(jù))。
具體怎么使用觸發(fā)器，今天我這里就不介紹了，園子里資料多的很。那么我今天要介紹的是什么呢？
前幾天在寫sql代碼的時(shí)候無(wú)意間發(fā)現(xiàn)了這么個(gè)問(wèn)題：就是我一直以為每當(dāng)動(dòng)態(tài)表中插入一條數(shù)據(jù)，觸發(fā)器就執(zhí)行一次，但是我這樣理解的話，當(dāng)批量插入數(shù)據(jù)的時(shí)候，觸發(fā)器執(zhí)行的次數(shù)和插入的行數(shù)相同，但是事實(shí)不是這樣。乘著今天有點(diǎn)時(shí)間，就想寫出來(lái)和大家分享下，講的不對(duì)請(qǐng)大家斧正！
下面，我就寫了個(gè)簡(jiǎn)單的例子供大家參考。
--我們要建觸發(fā)器的動(dòng)態(tài)表
Create table Table_a
(
ID int identity(1,1),--自增ID
Content nvarchar(50),
UpdateIDForTrigger int
)
然后我們?cè)谠摫砩蟿?chuàng)建一個(gè)觸發(fā)器
Create TRIGGER [dbo].[Table_a_Ins]
ON [dbo].[Table_a]
AFTER INSERT
AS
BEGIN
declare @ID int
set @ID=(select ID from inserted)
--更新Table_a表中的UpdateIDForTrigger字段的值，為了能更明顯的看出實(shí)時(shí)執(zhí)行的效果
UPDATE Table_a
SET UpdateIDForTrigger = (@ID+10)--為了能看出不同，就直接將比ID大10的值作為變量賦值
WHERE ID = @ID;
END
接下來(lái)，我們按照普通一條條的插入結(jié)果測(cè)試下：
--給信息表添加數(shù)據(jù)
insert into Table_a(Content) values('信息一');
insert into Table_a(Content) values('信息二');
然后查詢下現(xiàn)在動(dòng)態(tài)表中的數(shù)據(jù)情況
select * from Table_a
查詢結(jié)果如圖：
我們可以看到觸發(fā)器執(zhí)行了。在每條數(shù)據(jù)插入的時(shí)候觸發(fā)器同時(shí)執(zhí)行了Update功能。
然后，我們要批量插入數(shù)據(jù)，為了方便我們插入，我們這里建立一張臨時(shí)的基本信息表：
--基本信息表
Create table Table_Info
(
ID int identity(1,1),
Content nvarchar(50)
)
然后插入數(shù)據(jù)
insert into Table_Info(Content) values('信息三');
insert into Table_Info(Content) values('信息四');
insert into Table_Info(Content) values('信息五');
insert into Table_Info(Content) values('信息六');
insert into Table_Info(Content) values('信息七');
insert into Table_Info(Content) values('信息八');
insert into Table_Info(Content) values('信息九');
insert into Table_Info(Content) values('信息十');
然后我們就可以批量插入數(shù)據(jù)到動(dòng)態(tài)表中了
insert into Table_a(Content)
select Content from Table_Info
這次重點(diǎn)來(lái)了，我們?cè)趫?zhí)行這個(gè)sql語(yǔ)句的時(shí)候消息框中會(huì)出現(xiàn)錯(cuò)誤提示：
有經(jīng)驗(yàn)的朋友會(huì)知道，這個(gè)錯(cuò)誤是由于多個(gè)結(jié)果用“=”賦值給一個(gè)變量導(dǎo)致的。
即：set @變量=(select 多行結(jié)果 from Table)
這個(gè)時(shí)候，我就疑惑了，問(wèn)題出在哪里了呢？不是觸發(fā)器在每插一條數(shù)據(jù)的時(shí)候執(zhí)行一次么？
于是，我將觸發(fā)器改了下：
Alter TRIGGER [dbo].[Table_a_Ins]
ON [dbo].[Table_a]
AFTER INSERT
AS
BEGIN
select ID from inserted;
END
然后再執(zhí)行上面的批量插入試試看，看看他inserted表中到底存的是什么值：
果然不出所料，inserted表中的結(jié)果并不是一條數(shù)據(jù)：
知道錯(cuò)誤的原因，我們操作起來(lái)就簡(jiǎn)單了，我們可以給inserted表建游標(biāo)，然后通過(guò)游標(biāo)來(lái)對(duì)批量插入的每行數(shù)據(jù)進(jìn)行編輯。下面是我們修改后的觸發(fā)器代碼：
Alter TRIGGER [dbo].[Table_a_Ins]
ON [dbo].[Table_a]
AFTER INSERT
AS
BEGIN
declare @ID int
declare cur_Insert cursor
for
select ID from inserted
open cur_Insert
fetch next from cur_Insert into @ID
while @@fetch_status=0
begin
UPDATE Table_a
SET UpdateIDForTrigger = (@ID+10)--為了能看出不同，就直接將比ID大10的值作為變量賦值
WHERE ID = @ID;
fetch next from cur_Insert into @ID
end
close cur_Insert
deallocate cur_Insert
END
然后，我們?cè)侔凑丈厦娴呐坎迦霐?shù)據(jù)，然后查詢下動(dòng)態(tài)表中的結(jié)果：
insert into Table_a(Content)
select Content from Table_Info;
select * from Table_a;
此時(shí)運(yùn)行沒(méi)有錯(cuò)誤提示了，運(yùn)行結(jié)果如下：
Alter TRIGGER [dbo].[Table_a_Ins]
ON [dbo].[Table_a]
AFTER INSERT
AS
BEGIN
UPDATE Table_a
SET UpdateIDForTrigger =inserted.ID+10
FROM inserted
Where Table_a.ID=inserted.ID
END
然后再批量插入了幾行數(shù)據(jù)，結(jié)果也是可以的。所以學(xué)無(wú)止境啊??！
總結(jié)下：觸發(fā)器運(yùn)行是每次執(zhí)行一次Insert操作或者是Update,Delete等操作的時(shí)候才執(zhí)行的。它的對(duì)象不是針對(duì)于修改的行數(shù)(即：每行修改的時(shí)候執(zhí)行)。
這個(gè)時(shí)候，觸發(fā)器就是我們想要的神器了。我們可以在那張動(dòng)態(tài)表上新建觸發(fā)器。觸發(fā)器的實(shí)質(zhì)就是個(gè)存儲(chǔ)過(guò)程，只不過(guò)他調(diào)用的時(shí)間是根據(jù)所建的動(dòng)態(tài)表發(fā)生該表而執(zhí)行(即：Insert新數(shù)據(jù)，Update或者Delete數(shù)據(jù))。
具體怎么使用觸發(fā)器，今天我這里就不介紹了，園子里資料多的很。那么我今天要介紹的是什么呢？
前幾天在寫sql代碼的時(shí)候無(wú)意間發(fā)現(xiàn)了這么個(gè)問(wèn)題：就是我一直以為每當(dāng)動(dòng)態(tài)表中插入一條數(shù)據(jù)，觸發(fā)器就執(zhí)行一次，但是我這樣理解的話，當(dāng)批量插入數(shù)據(jù)的時(shí)候，觸發(fā)器執(zhí)行的次數(shù)和插入的行數(shù)相同，但是事實(shí)不是這樣。乘著今天有點(diǎn)時(shí)間，就想寫出來(lái)和大家分享下，講的不對(duì)請(qǐng)大家斧正！
下面，我就寫了個(gè)簡(jiǎn)單的例子供大家參考。
復(fù)制代碼 代碼如下:
--我們要建觸發(fā)器的動(dòng)態(tài)表
Create table Table_a
(
ID int identity(1,1),--自增ID
Content nvarchar(50),
UpdateIDForTrigger int
)
然后我們?cè)谠摫砩蟿?chuàng)建一個(gè)觸發(fā)器
復(fù)制代碼 代碼如下:
Create TRIGGER [dbo].[Table_a_Ins]
ON [dbo].[Table_a]
AFTER INSERT
AS
BEGIN
declare @ID int
set @ID=(select ID from inserted)
--更新Table_a表中的UpdateIDForTrigger字段的值，為了能更明顯的看出實(shí)時(shí)執(zhí)行的效果
UPDATE Table_a
SET UpdateIDForTrigger = (@ID+10)--為了能看出不同，就直接將比ID大10的值作為變量賦值
WHERE ID = @ID;
END
接下來(lái)，我們按照普通一條條的插入結(jié)果測(cè)試下：
復(fù)制代碼 代碼如下:
--給信息表添加數(shù)據(jù)
insert into Table_a(Content) values('信息一');
insert into Table_a(Content) values('信息二');
然后查詢下現(xiàn)在動(dòng)態(tài)表中的數(shù)據(jù)情況
復(fù)制代碼 代碼如下:
select * from Table_a
查詢結(jié)果如圖：
我們可以看到觸發(fā)器執(zhí)行了。在每條數(shù)據(jù)插入的時(shí)候觸發(fā)器同時(shí)執(zhí)行了Update功能。
然后，我們要批量插入數(shù)據(jù)，為了方便我們插入，我們這里建立一張臨時(shí)的基本信息表：
復(fù)制代碼 代碼如下:
--基本信息表
Create table Table_Info
(
ID int identity(1,1),
Content nvarchar(50)
)
然后插入數(shù)據(jù)
復(fù)制代碼 代碼如下:
insert into Table_Info(Content) values('信息三');
insert into Table_Info(Content) values('信息四');
insert into Table_Info(Content) values('信息五');
insert into Table_Info(Content) values('信息六');
insert into Table_Info(Content) values('信息七');
insert into Table_Info(Content) values('信息八');
insert into Table_Info(Content) values('信息九');
insert into Table_Info(Content) values('信息十');
然后我們就可以批量插入數(shù)據(jù)到動(dòng)態(tài)表中了
復(fù)制代碼 代碼如下:
insert into Table_a(Content)
select Content from Table_Info
這次重點(diǎn)來(lái)了，我們?cè)趫?zhí)行這個(gè)sql語(yǔ)句的時(shí)候消息框中會(huì)出現(xiàn)錯(cuò)誤提示：
有經(jīng)驗(yàn)的朋友會(huì)知道，這個(gè)錯(cuò)誤是由于多個(gè)結(jié)果用“=”賦值給一個(gè)變量導(dǎo)致的。
即：set @變量=(select 多行結(jié)果 from Table)
這個(gè)時(shí)候，我就疑惑了，問(wèn)題出在哪里了呢？不是觸發(fā)器在每插一條數(shù)據(jù)的時(shí)候執(zhí)行一次么？
于是，我將觸發(fā)器改了下：
復(fù)制代碼 代碼如下:
Alter TRIGGER [dbo].[Table_a_Ins]
ON [dbo].[Table_a]
AFTER INSERT
AS
BEGIN
select ID from inserted;
END
然后再執(zhí)行上面的批量插入試試看，看看他inserted表中到底存的是什么值：
果然不出所料，inserted表中的結(jié)果并不是一條數(shù)據(jù)：
知道錯(cuò)誤的原因，我們操作起來(lái)就簡(jiǎn)單了，我們可以給inserted表建游標(biāo)，然后通過(guò)游標(biāo)來(lái)對(duì)批量插入的每行數(shù)據(jù)進(jìn)行編輯。下面是我們修改后的觸發(fā)器代碼：
復(fù)制代碼 代碼如下:
Alter TRIGGER [dbo].[Table_a_Ins]
ON [dbo].[Table_a]
AFTER INSERT
AS
BEGIN
declare @ID int
declare cur_Insert cursor
for
select ID from inserted
open cur_Insert
fetch next from cur_Insert into @ID
while @@fetch_status=0
begin
UPDATE Table_a
SET UpdateIDForTrigger = (@ID+10)--為了能看出不同，就直接將比ID大10的值作為變量賦值
WHERE ID = @ID;
fetch next from cur_Insert into @ID
end
close cur_Insert
deallocate cur_Insert
END
然后，我們?cè)侔凑丈厦娴呐坎迦霐?shù)據(jù)，然后查詢下動(dòng)態(tài)表中的結(jié)果：
復(fù)制代碼 代碼如下:
insert into Table_a(Content)
select Content from Table_Info;
select * from Table_a;
此時(shí)運(yùn)行沒(méi)有錯(cuò)誤提示了，運(yùn)行結(jié)果如下：
這樣，批量插入插入數(shù)據(jù)時(shí)觸發(fā)器也能用了。
然后結(jié)合了幾位前輩的建議，再改了下觸發(fā)器的代碼。將上面的游標(biāo)改成了下面的方式:
復(fù)制代碼 代碼如下:
Alter TRIGGER [dbo].[Table_a_Ins]
ON [dbo].[Table_a]
AFTER INSERT
AS
BEGIN
UPDATE Table_a
SET UpdateIDForTrigger =inserted.ID+10
FROM inserted
Where Table_a.ID=inserted.ID
END
然后再批量插入了幾行數(shù)據(jù)，結(jié)果也是可以的。所以學(xué)無(wú)止境啊??！
總結(jié)下：觸發(fā)器運(yùn)行是每次執(zhí)行一次Insert操作或者是Update,Delete等操作的時(shí)候才執(zhí)行的。它的對(duì)象不是針對(duì)于修改的行數(shù)(即：每行修改的時(shí)候執(zhí)行)。
相關(guān)文章
SQLServer 連接異常與解決方法歸納，大家可以參考下解決方法。2009-07-07
簡(jiǎn)析SQL Server數(shù)據(jù)庫(kù)用視圖來(lái)處理復(fù)雜的數(shù)據(jù)查詢關(guān)系
本文我們主要介紹了SQL Server數(shù)據(jù)庫(kù)用視圖來(lái)處理復(fù)雜的數(shù)據(jù)查詢關(guān)系的相關(guān)知識(shí)，以及視圖的優(yōu)缺點(diǎn)和創(chuàng)建方式以及注意事項(xiàng)的相關(guān)知識(shí)，需要的朋友可以參考下2015-08-08
這篇文章介紹了SQL SERVER使用表分區(qū)優(yōu)化性能的方法，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2022-04-04
SQL SERVER 數(shù)據(jù)庫(kù)備份代碼實(shí)例
這篇文章主要介紹了SQL SERVER數(shù)據(jù)庫(kù)備份，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-03-03
我也有微信朋友圈了 Android實(shí)現(xiàn)
最近寫了一個(gè)簡(jiǎn)單的朋友圈程序，包含了朋友圈的列表實(shí)現(xiàn)，視頻的錄制、預(yù)覽與上傳，圖片可選擇拍照或者從相冊(cè)選取，從相冊(cè)選取可以一次選擇多張照片，并且限制照片的張數(shù)，想擁有真正屬于自己的朋友圈嗎？快來(lái)圍觀2016-05-05
sqlserver給表添加新字段、給表和字段添加備注、更新備注及查詢備注(sql語(yǔ)句)
這篇文章主要介紹了sqlserver給表添加新字段、給表和字段添加備注、更新備注及查詢備注,文中給大家介紹了Sqlserver新增帶備注字段的語(yǔ)句，需要的朋友可以參考下2022-10-10
sql語(yǔ)句優(yōu)化之SQL Server(詳細(xì)整理)
這篇文章主要介紹了sql語(yǔ)句優(yōu)化之SQL Server篇，整理的比較詳細(xì)，推薦收藏2014-07-07
SQLServer 2008數(shù)據(jù)庫(kù)降級(jí)到2005低版本
SQLServer 2008R2備份的數(shù)據(jù)庫(kù)還原到2008上面時(shí)報(bào)錯(cuò)引發(fā)的思考，如何把SQLServer數(shù)據(jù)庫(kù)從高版本降級(jí)到低版本？本文為大家解答2016-11-11
Insus.NET嘗試寫了程序并做了測(cè)試，可以得到預(yù)期的結(jié)果，SQL代碼可參考和學(xué)習(xí)之用。2011-09-09
sqlserver之datepart和datediff應(yīng)用查找當(dāng)天上午和下午的數(shù)據(jù)
這篇文章主要介紹了sqlserver之datepart和datediff應(yīng)用查找當(dāng)天上午和下午的數(shù)據(jù),非常不錯(cuò)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2019-08-08