NopCommerce上二次开发 触发器记录

最近要在NopCommerce上二次开发。

开发也就算了，该项目的架构设计很好，但性能不可谓不低。

扯远了，为了保持项目以后升级顺利，开次开发不允许在原项目基础上大改，只能以插件形式开发……

因一个功能，不好改代码，所以在数据层用触发器实现。代码记录在此

USE [NopCommerce]
GO
/****** Object:  Trigger [dbo].[InsertAffiliate]    Script Date: 2014/7/22 11:10:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:        崔大鹏
-- Create date: 2014.7.21
-- Description:    <Description,,> 需要去affiate 的外键关联
-- =============================================
ALTER TRIGGER [dbo].[InsertAffiliate]
   ON  [dbo].[Customer]
   AFTER INSERT,UPDATE
AS 

BEGIN


declare @CustomUserName nvarchar(1000)
select @CustomUserName=inserted.Username from inserted
 If (@CustomUserName is not null) 
  begin 
   -- print('用户名不为空（实注册用户，临时用户不进)
Declare @CustomId int
declare @errno int 
DECLARE @AddressId  int
DECLARE @AffiliateId  int

DECLARE @User_FirstName  nvarchar(100)
DECLARE @User_LastName  nvarchar(100)
DECLARE @Company  nvarchar(100)

select @CustomId=inserted.Id from inserted 


 -- print('不存在关联AFF)
   if not exists(select 1 from   P_CustomId_AffiliateId P WHERE P.Id=@CustomId)
   BEGIN

   Begin TransAction  

   select @AddressId from  CustomerAddresses as Ca WHERE CA.Customer_Id=@CustomId
 -- print('关联CustomerAddresses 表 查出addressge表的ID

 -- print('如果没查到，插入一条。ADDRESS 并插入一条关联。
if(@AddressId is null)
begin

--with sr as( select * from GenericAttribute with nolock
--                        where EntityId=@CustomId and KeyGroup='Customer'
--                        )
--select @User_FirstName=Value from sr
--where sr.[Key]='FirstName'

--select @User_LastName=Value from sr 

with sr as(select  case [Key] when 'FirstName' THEN VALUE   END  AS 'FirstName',case [Key] when 'LastName' THEN VALUE   END  AS 'LastName',  case [Key] when 'Company' THEN VALUE   END  AS 'Company'
   FROM [NopCommerce].[dbo].[GenericAttribute]
  where [EntityId]=@CustomId)

  select @User_FirstName=STUFF((SELECT ',' + FirstName  
         FROM sr AS G2 FOR XML PATH('')), 1, 1, '') ,@User_LastName=STUFF((SELECT ',' + LastName  
         FROM sr AS G2 FOR XML PATH('')), 1, 1, ''),@Company= STUFF((SELECT ',' + Company  
         FROM sr AS G2 FOR XML PATH('')), 1, 1, '')

INSERT INTO [dbo].[Address]
           ([FirstName]
           ,[LastName]
           ,[Email]
           ,[Company]
           ,[CountryId]
           ,[StateProvinceId]
           ,[City]
           ,[Address1]
           ,[Address2]
           ,[ZipPostalCode]
           ,[PhoneNumber]
           ,[FaxNumber]
           ,[CreatedOnUtc])
     VALUES
           (@User_FirstName
           ,@User_LastName
           ,@CustomUserName
           ,@Company
           ,NULL
           ,NULL
           ,''
           ,''
           ,''
           ,''
           ,''
           ,''
           ,GETDATE())
SELECT @AddressId=@@IDENTITY 

set @errno=@errno+@@error

INSERT INTO [dbo].[CustomerAddresses]
           ([Customer_Id]
           ,[Address_Id])
     VALUES
           (@CustomId
           ,@AddressId)
set @errno=@errno+@@error
end

 -- print('插入Affiliate 以上对ADDRESS表的处理，因为，AFF表有外键关联，ADDRESS无数据，不能INSERT
insert into Affiliate  ([AddressId]
           ,[Deleted]
           ,[Active])
           values(@AddressId,0,1)
SELECT @AffiliateId=@@IDENTITY 

set @errno=@errno+@@error
-- print('插入P_CustomId_AffiliateId
insert into P_CustomId_AffiliateId
           values(@CustomId,@AffiliateId)

set @errno=@errno+@@error
 If @errno>0 
  begin 
   -- print('事务处理失败，回滚事务！')
   rollback TransAction 
  end 
 Else
  Begin 
   -- print('事务处理成功，提交事务！')
   Commit TransAction
  End 

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for trigger here



  end 

   END  
   


END