1 USE [NopCommerce]  2 GO  3 /****** Object:  Trigger [dbo].[InsertAffiliate]    Script Date: 2014/7/22 11:10:03 ******/  4 SET ANSI_NULLS ON  5 GO  6 SET QUOTED_IDENTIFIER ON  7 GO  8   9  10 -- ============================================= 11 -- Author:        崔大鹏 12 -- Create date: 2014.7.21 13 -- Description:    <Description,,> 需要去affiate 的外键关联 14 -- ============================================= 15 ALTER TRIGGER [dbo].[InsertAffiliate] 16    ON  [dbo].[Customer] 17    AFTER INSERT,UPDATE 18 AS  19  20 BEGIN 21  22  23 declare @CustomUserName nvarchar(1000) 24 select @CustomUserName=inserted.Username from inserted 25  If (@CustomUserName is not null)  26   begin  27    -- print(‘用户名不为空（实注册用户，临时用户不进) 28 Declare @CustomId int 29 declare @errno int  30 DECLARE @AddressId  int 31 DECLARE @AffiliateId  int 32  33 DECLARE @User_FirstName  nvarchar(100) 34 DECLARE @User_LastName  nvarchar(100) 35 DECLARE @Company  nvarchar(100) 36  37 select @CustomId=inserted.Id from inserted  38  39  40  -- print(‘不存在关联AFF) 41    if not exists(select 1 from   P_CustomId_AffiliateId P WHERE P.Id=@CustomId) 42    BEGIN 43  44    Begin TransAction   45  46    select @AddressId from  CustomerAddresses as Ca WHERE CA.Customer_Id=@CustomId 47  -- print(‘关联CustomerAddresses 表 查出addressge表的ID 48  49  -- print(‘如果没查到，插入一条。ADDRESS 并插入一条关联。 50 if(@AddressId is null) 51 begin 52  53 --with sr as( select * from GenericAttribute with nolock 54 --                        where EntityId=@CustomId and KeyGroup=‘Customer‘ 55 --                        ) 56 --select @User_FirstName=Value from sr 57 --where sr.[Key]=‘FirstName‘ 58  59 --select @User_LastName=Value from sr  60  61 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‘ 62    FROM [NopCommerce].[dbo].[GenericAttribute] 63   where [EntityId]=@CustomId) 64  65   select @User_FirstName=STUFF((SELECT ‘,‘ + FirstName   66          FROM sr AS G2 FOR XML PATH(‘‘)), 1, 1, ‘‘) ,@User_LastName=STUFF((SELECT ‘,‘ + LastName   67          FROM sr AS G2 FOR XML PATH(‘‘)), 1, 1, ‘‘),@Company= STUFF((SELECT ‘,‘ + Company   68          FROM sr AS G2 FOR XML PATH(‘‘)), 1, 1, ‘‘) 69  70 INSERT INTO [dbo].[Address] 71            ([FirstName] 72            ,[LastName] 73            ,[Email] 74            ,[Company] 75            ,[CountryId] 76            ,[StateProvinceId] 77            ,[City] 78            ,[Address1] 79            ,[Address2] 80            ,[ZipPostalCode] 81            ,[PhoneNumber] 82            ,[FaxNumber] 83            ,[CreatedOnUtc]) 84      VALUES 85            (@User_FirstName 86            ,@User_LastName 87            ,@CustomUserName 88            ,@Company 89            ,NULL 90            ,NULL 91            ,‘‘ 92            ,‘‘ 93            ,‘‘ 94            ,‘‘ 95            ,‘‘ 96            ,‘‘ 97            ,GETDATE()) 98 SELECT @AddressId=@@IDENTITY  99 100 set @errno=@errno+@@error101 102 INSERT INTO [dbo].[CustomerAddresses]103            ([Customer_Id]104            ,[Address_Id])105      VALUES106            (@CustomId107            ,@AddressId)108 set @errno=@errno+@@error109 end110 111  -- print(‘插入Affiliate 以上对ADDRESS表的处理，因为，AFF表有外键关联，ADDRESS无数据，不能INSERT112 insert into Affiliate  ([AddressId]113            ,[Deleted]114            ,[Active])115            values(@AddressId,0,1)116 SELECT @AffiliateId=@@IDENTITY 117 118 set @errno=@errno+@@error119 -- print(‘插入P_CustomId_AffiliateId120 insert into P_CustomId_AffiliateId121            values(@CustomId,@AffiliateId)122 123 set @errno=@errno+@@error124  If @errno>0 125   begin 126    -- print(‘事务处理失败，回滚事务！‘)127    rollback TransAction 128   end 129  Else130   Begin 131    -- print(‘事务处理成功，提交事务！‘)132    Commit TransAction133   End 134 135     -- SET NOCOUNT ON added to prevent extra result sets from136     -- interfering with SELECT statements.137     SET NOCOUNT ON;138     -- Insert statements for trigger here139 140 141 142   end 143 144    END  145    146 147 148 END