NopCommerce上二次开发 触发器记录
最近要在NopCommerce上二次开发。
开发也就算了，该项目的架构设计很好，但性能不可谓不低。
扯远了，为了保持项目以后升级顺利，开次开发不允许在原项目基础上大改，只能以插件形式开发……
因一个功能，不好改代码，所以在数据层用触发器实现。代码记录在此
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+@@error 101 102 INSERT INTO [dbo].[CustomerAddresses] 103 ([Customer_Id] 104 ,[Address_Id]) 105 VALUES 106 (@CustomId 107 ,@AddressId) 108 set @errno=@errno+@@error 109 end 110 111 -- print('插入Affiliate 以上对ADDRESS表的处理，因为，AFF表有外键关联，ADDRESS无数据，不能INSERT 112 insert into Affiliate ([AddressId] 113 ,[Deleted] 114 ,[Active]) 115 values(@AddressId,0,1) 116 SELECT @AffiliateId=@@IDENTITY 117 118 set @errno=@errno+@@error 119 -- print('插入P_CustomId_AffiliateId 120 insert into P_CustomId_AffiliateId 121 values(@CustomId,@AffiliateId) 122 123 set @errno=@errno+@@error 124 If @errno>0 125 begin 126 -- print('事务处理失败，回滚事务！') 127 rollback TransAction 128 end 129 Else 130 Begin 131 -- print('事务处理成功，提交事务！') 132 Commit TransAction 133 End 134 135 -- SET NOCOUNT ON added to prevent extra result sets from 136 -- interfering with SELECT statements. 137 SET NOCOUNT ON; 138 -- Insert statements for trigger here 139 140 141 142 end 143 144 END 145 146 147 148 END
