
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_BuyLimit_Category]') AND type = 'F')
ALTER TABLE [dbo].[BuyLimit]  WITH CHECK ADD  CONSTRAINT [FK_BuyLimit_Category] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_CatBrand_Brand]') AND type = 'F')
ALTER TABLE [dbo].[CatBrand]  WITH CHECK ADD  CONSTRAINT [FK_CatBrand_Brand] FOREIGN KEY([BrandID])
REFERENCES [dbo].[Brand] ([BrandID])
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_CatBrand_Category]') AND type = 'F')
ALTER TABLE [dbo].[CatBrand]  WITH CHECK ADD  CONSTRAINT [FK_CatBrand_Category] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_CouponMember_Coupon]') AND type = 'F')
ALTER TABLE [dbo].[CouponMember]  WITH CHECK ADD  CONSTRAINT [FK_CouponMember_Coupon] FOREIGN KEY([CouponID])
REFERENCES [dbo].[Coupon] ([CouponID])
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_CouponMember_Member]') AND type = 'F')
ALTER TABLE [dbo].[CouponMember]  WITH CHECK ADD  CONSTRAINT [FK_CouponMember_Member] FOREIGN KEY([MemberID])
REFERENCES [dbo].[Member] ([MemberID])
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_CouponsOrderItems_Coupons]') AND type = 'F')
ALTER TABLE [dbo].[CouponsOrderItems]  WITH CHECK ADD  CONSTRAINT [FK_CouponsOrderItems_Coupons] FOREIGN KEY([CouponsID])
REFERENCES [dbo].[Coupons] ([CouponsID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_CouponsUsedItems_Coupons]') AND type = 'F')
ALTER TABLE [dbo].[CouponsUsedItems]  WITH CHECK ADD  CONSTRAINT [FK_CouponsUsedItems_Coupons] FOREIGN KEY([CouponsID])
REFERENCES [dbo].[Coupons] ([CouponsID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_CouponsUsedItems_Orders]') AND type = 'F')
ALTER TABLE [dbo].[CouponsUsedItems]  WITH CHECK ADD  CONSTRAINT [FK_CouponsUsedItems_Orders] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_GiftOrderItem_GiftOrder]') AND type = 'F')
ALTER TABLE [dbo].[GiftOrderItem]  WITH CHECK ADD  CONSTRAINT [FK_GiftOrderItem_GiftOrder] FOREIGN KEY([GiftOrderID])
REFERENCES [dbo].[GiftOrder] ([GiftOrderID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_NewProduct_Product]') AND type = 'F')
ALTER TABLE [dbo].[NewProduct]  WITH CHECK ADD  CONSTRAINT [FK_NewProduct_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Product] ([ProductID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_News_Article_News_Category]') AND type = 'F')
ALTER TABLE [dbo].[News_Article]  WITH CHECK ADD  CONSTRAINT [FK_News_Article_News_Category] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[News_Category] ([CategoryID])
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_OrderProduct_Orders]') AND type = 'F')
ALTER TABLE [dbo].[OrderProduct]  WITH NOCHECK ADD  CONSTRAINT [FK_OrderProduct_Orders] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
ON DELETE CASCADE
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[OrderProduct] CHECK CONSTRAINT [FK_OrderProduct_Orders]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_PmtGenCoupons_Promotion]') AND type = 'F')
ALTER TABLE [dbo].[PmtGenCoupons]  WITH CHECK ADD  CONSTRAINT [FK_PmtGenCoupons_Promotion] FOREIGN KEY([PromotionID])
REFERENCES [dbo].[Promotion] ([PromotionID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_PmtProduct_Promotion]') AND type = 'F')
ALTER TABLE [dbo].[PmtProduct]  WITH CHECK ADD  CONSTRAINT [FK_PmtProduct_Promotion] FOREIGN KEY([PromotionID])
REFERENCES [dbo].[Promotion] ([PromotionID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_ProductField_CategoryField]') AND type = 'F')
ALTER TABLE [dbo].[ProductField]  WITH CHECK ADD  CONSTRAINT [FK_ProductField_CategoryField] FOREIGN KEY([CategoryFieldID])
REFERENCES [dbo].[CategoryField] ([CategoryFieldID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_PromotionMemberGroup_Promotion]') AND type = 'F')
ALTER TABLE [dbo].[PromotionMemberGroup]  WITH CHECK ADD  CONSTRAINT [FK_PromotionMemberGroup_Promotion] FOREIGN KEY([PromotionID])
REFERENCES [dbo].[Promotion] ([PromotionID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_ShippPay_Shipping]') AND type = 'F')
ALTER TABLE [dbo].[ShippPay]  WITH CHECK ADD  CONSTRAINT [FK_ShippPay_Shipping] FOREIGN KEY([ShippID])
REFERENCES [dbo].[Shipping] ([ShippingID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_ShippPay_ShippingArea]') AND type = 'F')
ALTER TABLE [dbo].[ShippPay]  WITH CHECK ADD  CONSTRAINT [FK_ShippPay_ShippingArea] FOREIGN KEY([ShippAreaID])
REFERENCES [dbo].[ShippingArea] ([ShippingAreaID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_SubscriptionsContent_Member]') AND type = 'F')
ALTER TABLE [dbo].[SubscriptionsContent]  WITH CHECK ADD  CONSTRAINT [FK_SubscriptionsContent_Member] FOREIGN KEY([MemberID])
REFERENCES [dbo].[Member] ([MemberID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_SubscriptionsContent_SubscriptionsOptions]') AND type = 'F')
ALTER TABLE [dbo].[SubscriptionsContent]  WITH CHECK ADD  CONSTRAINT [FK_SubscriptionsContent_SubscriptionsOptions] FOREIGN KEY([SubscriptionsOptionsID])
REFERENCES [dbo].[SubscriptionsOptions] ([SubscriptionsOptionsID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_SubscriptionsOptions_Subscriptions]') AND type = 'F')
ALTER TABLE [dbo].[SubscriptionsOptions]  WITH CHECK ADD  CONSTRAINT [FK_SubscriptionsOptions_Subscriptions] FOREIGN KEY([SubscriptionsID])
REFERENCES [dbo].[Subscriptions] ([SubscriptionsID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

CREATE TRIGGER [T_GiftOrderItem_Update_Gift] ON [dbo].[GiftOrderItem] 
FOR INSERT 
AS
BEGIN
	Declare @giftID int
	Select @giftID = GiftID From Inserted
	Update Gift Set AlreadyExchaged = AlreadyExchaged + 1 Where GiftID = @giftID
END
GO

CREATE trigger [UpdateTopPrice] ON [dbo].[NewBid]
for insert
as
begin
	declare @pid int
	select @pid=NewProductID from inserted
	update NewProduct 
	set TopPrice = (select Max(BidPrice) from NewBid where NewProductID = @pid),PeopleSum=(select count(NewBidID) from NewBid where NewProductID = @pid) where NewProductID = @pid
end
GO

CREATE trigger [UpdateBrand] ON [dbo].[Activity]
for insert,update,delete
as
begin
	declare @actID int
	declare @bid int
	IF Exists(Select 1 From inserted)
	begin
		select @actID=ActivityID,@bid=BrandID from inserted
		update Brand 
		set ActID = @actID where BrandID = @bid
	end
	ELSE IF  Exists(Select 1 From deleted)
	begin
		declare @type int
		select @type=Type,@bid = BrandID from deleted
		if(@type =2)
		begin
			update Brand set ActID = 0 where BrandID = @bid
		end
	end
	
end
GO

CREATE trigger [T_Category_Update_CategoryID] on [dbo].[Category]
for update
as
begin
	if update(CategoryID)
	begin
		declare @categoryID decimal(30,0)
		Select @categoryID = CategoryID From Inserted
		update Category set ParentID = cast(@categoryID/1000 + 0.5 as decimal(30,0)) - 1 Where CategoryID = @categoryID
	end
end
GO

CREATE TRIGGER [T_Coupons_Delete_Promotion] ON [dbo].[Coupons] 
FOR DELETE 
AS
BEGIN
	Delete From Promotion Where PromotionID In (Select PromotionID From Deleted)
END
GO

CREATE TRIGGER [T_Update_Orders_IsToMessage] ON [dbo].[Orders]
FOR UPDATE
AS
Begin
	IF Update(Status)
	Begin
		declare @status int
		declare @status_old int
		declare @orderid int
		select @status = status,@orderid = OrderID from inserted
		select @status_old = status from deleted
		if(@status = 2 and @status_old <> 2)
		begin
			update orders set IsToMessage = 0 where OrderID = @orderid
		end
	End
End
GO

CREATE TRIGGER [T_Product_Update_Storage] ON [dbo].[Product]
FOR UPDATE
AS
Begin
	IF Update(Storage)
	Begin
		Declare @productID int
		Declare @old_storage int
		Declare @new_storage int
		Select @productID = ProductID,@old_storage = Storage From Deleted
		Select @new_storage = Storage From Inserted
		Update Product Set SaleNum = SaleNum + @old_storage - @new_storage Where ProductID = @productID
	End
End
GO

CREATE TRIGGER [T_Bind_Relation] ON [dbo].[Product]
FOR INSERT, UPDATE, DELETE   
AS
Begin
	Declare @packageInfo_old nvarchar(4000)
	Declare @packageInfo_new nvarchar(4000)
	Declare @ispackaged Int,@pID Int
	Declare @rproductIDs_old nvarchar(100),@rproductIDs_new nvarchar(100),@temp_old nvarchar(4000),@temp_new nvarchar(4000)
	IF Exists(Select 1 From inserted) and Exists(Select 1 From deleted) 
	Begin
		IF Update(PackageInfo)
		Begin
			Select @packageInfo_old = PackageInfo From Deleted
			Select @packageInfo_new = PackageInfo,@ispackaged = IsPackaged,@pID = ProductID From Inserted
			IF(@packageInfo_old <> @packageInfo_new)
			IF(@ispackaged = 1)
			Begin
				Set @temp_old = REPLACE(@packageInfo_old,'{"RelationProductID":"','')
				Set @temp_new = REPLACE(@packageInfo_new,'{"RelationProductID":"','')
				IF(CHARINDEX('","Product":[',@temp_old) > 0)
					Set @rproductIDs_old = SUBSTRING(@temp_old, 1, CHARINDEX('","Product":[',@temp_old)-1)
				Set @rproductIDs_new = SUBSTRING(@temp_new, 1, CHARINDEX('","Product":[',@temp_new)-1)
				Update Product Set PackageInfo = REPLACE(dbo.f_l_removesign(dbo.f_r_removesign(REPLACE(dbo.f_addsign(PackageInfo),dbo.f_addsign(@pID),','))),' ','') Where ProductID In (Select val From dbo.Func_SplitInt(@rproductIDs_old))
				Update Product Set PackageInfo = REPLACE(dbo.f_l_removesign(PackageInfo + ',' + cast(@pID as nvarchar(10))),' ','') Where ProductID In (Select val From dbo.Func_SplitInt(@rproductIDs_new))
			End
		End
	End
	ELSE IF Exists(Select 1 From inserted)
	Begin
		Select @packageInfo_new = PackageInfo,@ispackaged = IsPackaged,@pID = ProductID From Inserted
		IF(@ispackaged = 1)
		Begin
			Set @temp_new = REPLACE(@packageInfo_new,'{"RelationProductID":"','')
			Set @rproductIDs_new = SUBSTRING(@temp_new, 1, CHARINDEX('","Product":[',@temp_new)-1)
			Update Product Set PackageInfo = REPLACE(dbo.f_l_removesign(PackageInfo + ',' + cast(@pID as nvarchar(10))),' ','') Where ProductID In (Select val From dbo.Func_SplitInt(@rproductIDs_new))
		End
	End
	ELSE IF Exists(Select 1 From deleted)
	Begin
		Select @packageInfo_old = PackageInfo,@ispackaged = IsPackaged,@pID = ProductID From Deleted
		IF(@ispackaged = 1)
		Begin
			Set @temp_old = REPLACE(@packageInfo_old,'{"RelationProductID":"','')
			IF(CHARINDEX('","Product":[',@temp_old) > 0)
				Set @rproductIDs_old = SUBSTRING(@temp_old, 1, CHARINDEX('","Product":[',@temp_old)-1)
			Update Product Set PackageInfo = REPLACE(dbo.f_l_removesign(dbo.f_r_removesign(REPLACE(dbo.f_addsign(PackageInfo),dbo.f_addsign(@pID),','))),' ','') Where ProductID In (Select val From dbo.Func_SplitInt(@rproductIDs_old))
		End
	End
End
GO

CREATE TRIGGER [T_Update_IsBuyLimited] ON [dbo].[BuyLimit]
FOR INSERT, DELETE 
AS
Begin
	Declare @productID int
	IF Exists(Select 1 From inserted)
	Begin
		Select @productID = ProductID From Inserted
		Update Product Set IsBuyLimited = 1 Where ProductID = @productID
	End
	ELSE IF Exists(Select 1 From deleted)
	Begin
		Update Product Set IsBuyLimited = 0
		Update Product Set IsBuyLimited = 1 Where ProductID In (Select ProductID From BuyLimit)
	End
End
GO

CREATE TRIGGER [T_PromotionActivity_Delete_Promotion] ON [dbo].[PromotionActivity] 
FOR DELETE 
AS
BEGIN
	Delete From Promotion Where PromotionActivityID In (Select PromotionActivityID From Deleted)
END
GO

CREATE TRIGGER [T_ShoppingCart_Delete_ShoppingCartCoupons] ON [dbo].[ShoppingCart] 
FOR DELETE 
AS
BEGIN
	Declare @cartID nvarchar(50)
	Declare @count int
	Select @cartID = CartID From Deleted
	
	Select @count = Count(RecordID) From ShoppingCart Where CartID = @cartID
	IF(@count = 0)
		Delete From ShoppingCartCoupons Where CartID = @cartID
END
