AbleCommerce Shopping Cart Software

Code: Select all

CREATE TABLE [dbo].[custom_ProductInventory](
	[ProductID] [int] NULL,
	[ProductVariantID] [int] NULL,
	[Sku] [nchar](40) NULL,
	[StockDate] [datetime] NULL CONSTRAINT [DF_custom_ProductInventory_StockDate]  DEFAULT (getdate()),
	[OriginalQuantity] [int] NULL,
	[NewQuantity] [int] NULL,
	[StockQuantity] [int] NULL
) ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[triggerProductStockHistory] 
   ON [dbo].[ac_Products] 
   FOR UPDATE
AS 
IF UPDATE(InStock)
BEGIN
	SET NOCOUNT ON;

 	INSERT INTO dbo.custom_ProductInventory
	SELECT
		INSERTED.ProductID,
		0,
		INSERTED.Sku,
		getdate(),
		DELETED.InStock,
		INSERTED.InStock,
		INSERTED.InStock - DELETED.InStock
	FROM
		INSERTED, DELETED
	WHERE
		(INSERTED.InStock - DELETED.InStock) <> 0

END
GO

CREATE TRIGGER [dbo].[triggerProductVariantsStockHistory] 
   ON [dbo].[ac_ProductVariants] 
   FOR UPDATE
AS 
IF UPDATE(InStock)
BEGIN
	SET NOCOUNT ON;

	INSERT INTO dbo.custom_ProductInventory
	SELECT
		INSERTED.ProductID,
		INSERTED.ProductVariantID,
		INSERTED.Sku,
		getdate(),
		DELETED.InStock,
		INSERTED.InStock,
		INSERTED.InStock - DELETED.InStock
	FROM
		INSERTED, DELETED
	WHERE
		(INSERTED.InStock - DELETED.InStock) <> 0

END
GO