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