sql常用小知识 ---触发器-错误的使用

如果一个表，会被频繁的写入数据。则不要使用触发器的方式去将本表的数据同步其他的表。如果这样做会导致错误：

向服务器录入数据：事务(进程 ID 73)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新

GO
/****** Object:  Trigger [dbo].[tr_DataSyncInsert]    Script Date: 03/19/2013 14:50:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<arvin>
-- Create date: <2012-11-29>
-- Description:	<insert同步数据>
-- =============================================
ALTER TRIGGER [dbo].[tr_DataSyncInsert] 
   ON  [dbo].[Open_AllItem]
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON;
	INSERT INTO dbo.Open_Item
	SELECT 
	*
	FROM dbo.Open_AllItem Where [Item_ID] IN (Select [Item_ID] From inserted)
	INSERT INTO dbo.Open_Url
	([CustomerID],[Brand_ID],[URL],[URLKEY])
	SELECT [CustomerID],[Brand_ID],[Item_URL],[Item_URLKey] FROM dbo.Open_AllItem Where [Item_ID] IN (Select [Item_ID] From inserted)
END

解决方式：

大家可以使用事务的方式进行数据的同步。

USE [IwomData]
GO
/****** Object:  StoredProcedure [dbo].[pr_InsertItemFromCrawler]    Script Date: 03/19/2013 14:48:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<arvin>
-- Create date: <2012-11-29>
-- Description:	<将抓取到的数据插入到Open_Item表>
-- =============================================
ALTER PROCEDURE [dbo].[pr_InsertItemFromCrawler] 
  @Task_ID int,
  @Brand_ID int,
  @Item_URL varchar(1500),
  @Item_URLKey varchar(32),
  @Item_Content text,
  @Item_Title nvarchar(200),
  @Item_SDate smalldatetime,
  @Item_ReleaseDate smalldatetime,
  @Item_Author nvarchar(150),
  @Item_ReplyCount int,
  @Item_VisitCount int,
  @Task_KeyWord nvarchar(80),
  @SearchEngine_ID int, 
  @Item_SiteName nvarchar(20), 
  @Item_Info nvarchar(500),
  @Item_Area nvarchar(500),
  @CustomerID int,
  @KeyWordID int
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @ITEM_ID INT;
	SET @ITEM_ID=0;
	declare @count int
	print @Brand_ID;
	print @Item_URLKey;
	set @count=0;
	select top 1 @count = 1 from dbo.Open_Url  with (nolock) where brand_id = @Brand_ID and URLKEY = @Item_URLKey and CustomerID=@CustomerID ;
	
	
	begin transaction tran_bank;
	declare @tran_error int;
    set @tran_error = 0;
    begin try
	if(@count = 0)
	begin
	    INSERT INTO dbo.Open_Url([CustomerID],[Brand_ID],[URL],[URLKEY])SELECT @CustomerID,@Brand_ID,@Item_URL,@Item_URLKey;
	    if(ISNULL(@Item_SiteName,'a')='a' or @Item_SiteName='')
	    begin
	    set @Item_SiteName=isnull(dbo.[fun_GetSiteName](@Item_URL),'')
	    end
		INSERT INTO dbo.Open_AllItem (Task_ID,Brand_ID,Item_URL,Item_URLKey,Item_Title,Item_Content
           ,Item_SDate,Item_ReleaseDate,Item_Author,Item_ReplyCount,Item_VisitCount,Task_KeyWord
           ,SearchEngine_ID,Item_Info,Item_SiteName,CustomerID,Item_Area,KeyWordID)
		VALUES
           (@Task_ID,@Brand_ID,@Item_URL,@Item_URLKey,@Item_Title,@Item_Content,@Item_SDate,@Item_ReleaseDate
			,@Item_Author,@Item_ReplyCount,@Item_VisitCount,@Task_KeyWord,@SearchEngine_ID,@Item_Info,@Item_SiteName,@CustomerID,@Item_Area,@KeyWordID)
		select @ITEM_ID=SCOPE_IDENTITY();
		INSERT INTO dbo.[Open_Item] (Task_ID,Brand_ID,Item_URL,Item_URLKey,Item_Title,Item_Content
           ,Item_SDate,Item_ReleaseDate,Item_Author,Item_ReplyCount,Item_VisitCount,Task_KeyWord
           ,SearchEngine_ID,Item_Info,Item_SiteName,CustomerID,Item_Area,KeyWordID,[Item_ID])
		VALUES
           (@Task_ID,@Brand_ID,@Item_URL,@Item_URLKey,@Item_Title,@Item_Content,@Item_SDate,@Item_ReleaseDate
			,@Item_Author,@Item_ReplyCount,@Item_VisitCount,@Task_KeyWord,@SearchEngine_ID,@Item_Info,@Item_SiteName,@CustomerID,@Item_Area,@KeyWordID,@ITEM_ID)
		select @ITEM_ID;
	end
	else 
		select 0;
	end try
	begin catch              
        set @tran_error = @tran_error + 1;
    end catch
    if (@tran_error > 0)
    begin
        --执行出错，回滚事务
        rollback tran;
        SELECT error_message(); 
    end
	else
    begin
        --没有异常，提交事务
        commit tran;
        SELECT @ITEM_ID;
    end
END