SQL Triggers

Question

Hi,
I need a help regarding SQL Triggers. I think I'm making a mistake that is why it is not executing well.

I'm developing an application which have PURCHASE, PURCHASEDETAILS, LOGS tables. What I need here is that when I make a purchase and when an entry is inserted or updated in PURCHASEDETAILS table a trigger should be fired and value of some fields of PURCHASEDETAILS shall be copied to LOGS table.

An image of the tables are shown below for a reference:

I need two triggers. One in PURCHASEDETAILS table to copy the ProductId, StockedQuantity, PurchaseId data to Logs table after insert.
Another trigger shall fire to update the stock based on the ProductId (as shown in the image). Unfortunately I'm not getting the desired result.

Can anyone help me?

Thank you.

CREATE TABLE [dbo].Suppliers ON [PRIMARY]
GO

CREATE TABLE [dbo].Category ON [PRIMARY]
GO

CREATE TABLE [dbo].Products ON [PRIMARY]
GO

ALTER TABLE [dbo].[Products] ADD CONSTRAINT [DF_Products_Discontinued] DEFAULT ((0)) FOR [Discontinued]
GO

ALTER TABLE [dbo].[Products] WITH CHECK ADD CONSTRAINT [FK_Products_Category] FOREIGN KEY([Categoryid])
REFERENCES [dbo].[Category] ([Categoryid])
GO

ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Category]
GO

ALTER TABLE [dbo].[Products] WITH CHECK ADD CONSTRAINT [FK_Products_Suppliers] FOREIGN KEY([Supplierid])
REFERENCES [dbo].[Suppliers] ([Supplierid])
GO

ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Suppliers]
GO

CREATE TABLE [dbo].Purchase ON [PRIMARY]
GO

ALTER TABLE [dbo].[Purchase] WITH CHECK ADD CONSTRAINT [FK_Purchase_Suppliers] FOREIGN KEY([Supplierid])
REFERENCES [dbo].[Suppliers] ([Supplierid])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Purchase] CHECK CONSTRAINT [FK_Purchase_Suppliers]
GO

CREATE TABLE [dbo].PurchaseDetails ON [PRIMARY]
GO

ALTER TABLE [dbo].[PurchaseDetails] ADD CONSTRAINT [DF_PurchaseDetails_RejectedQty] DEFAULT ((0)) FOR [RejectedQty]
GO

ALTER TABLE [dbo].[PurchaseDetails] ADD CONSTRAINT [DF_PurchaseDetails_TaxPrice] DEFAULT ((0)) FOR [TaxAmt]
GO

ALTER TABLE [dbo].[PurchaseDetails] ADD CONSTRAINT [DF_PurchaseDetails_Discount] DEFAULT ((0)) FOR [Discount]
GO

ALTER TABLE [dbo].[PurchaseDetails] WITH CHECK ADD CONSTRAINT [FK_PurchaseDetails_Purchase] FOREIGN KEY([PurchaseId])
REFERENCES [dbo].[Purchase] ([PurchaseId])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[PurchaseDetails] CHECK CONSTRAINT [FK_PurchaseDetails_Purchase]
GO

CREATE TABLE [dbo].InventoryLogs ON [PRIMARY]
GO

ALTER TABLE [dbo].[InventoryLogs] ADD CONSTRAINT [DF_PurchaseLogs_Stock] DEFAULT ((0)) FOR [Stock]
GO

INSERT STATEMENTS

CREATE PROCEDURE [dbo].[Purchase_Add]
(@purchasedate date,@supplierid bigint,@freightcharges decimal(18,3),@createdby varchar(50), @purchasedetails Add_PurchaseDetails Readonly)
AS
DECLARE @purchaseid as bigint;

BEGIN TRY
BEGIN TRANSACTION
INSERT INTO Purchase(PurchaseDate,Supplierid,FreightCharges,Createdby,Createdon)
VALUES(@purchasedate,@supplierid,@freightcharges,@createdby,getdate());

	SET @purchaseid=IDENT_CURRENT('Purchase');  

	INSERT INTO PurchaseDetails  
	SELECT @purchaseid,Productid,OrderQty,ReceivedQty,RejectedQty,StockedQty,UnitPrice,TaxAmt,Discount  
	FROM @purchasedetails;  

COMMIT TRANSACTION  

END TRY

BEGIN CATCH
IF @@TRANCOUNT>
0
ROLLBACK TRANSACTION

TRIGGER
Unfortunately, this was not working

INSERT INTO InventoryLogs(ProductId,Movement,Quantity,TransactionId,Stock)
SELECT Productid,'IN',StockedQty,PurchaseId,StockedQty
FROM inserted i
WHERE Productid=i.Productid;

Answer 1

Hi @Ranjith ,

I feel one trigger will suffice for both the operations if I understand correctly. Refer to the trigger below ( SQL Server implementation )

CREATE TRIGGER OnPurchaseDetailsInsert ON PURCHASEDETAILS  
AFTER INSERT  
AS  
BEGIN  
    SET NOCOUNT ON;  
    DECLARE @CurrentStock INT  
  
    SELECT TOP 1 @CurrentStock = Stock  
    FROM LOGS  
    WHERE ProductId = INSERTED.ProductId  
    ORDER BY PurchaseLogId DESC  
  
    ISNULL(@CurrentStock, 0)  
  
    INSERT INTO LOGS VALUES (INSERTED.ProductId, 'IN', INSERTED.StockedQty, INSERTED.PuchaseId, @CurrentStock)   
END  
GO  

If you want an implementation with two triggers, do let me know, I can post that too or you can get an idea from the above!

Hope this helps!

Answer 2

The trigger code that Showndarya is full of errors. and will not even compile.

This one may be somewhat closer to what Ranjith is looking for:

CREATE TRIGGER purchasedetails_tri ON dho.PurchseDetails AFTER INSERT AS
   INSERT logs(ProductID, Movement, Quantity, TransactionID, Stock)
      SELECT ProuctID, '??', StockedQty, TransactionID, 
             (SELECT SUM(StockedQty)
              FROM   purchasedetails pd
              WHERE  pd.ProductId = i.ProductID)
      FROM   inserted

I left Movement as ?? since I have no idea how define that column. I'm also uncertaing about StockedQty. I have a feeling that here are more columns in the table that we don't see.

However, I need to see that storing the full quantity after the operation in the LOGS table is dubious. Imagine that there are millions of rows for the same product. Should you really sum all rows from 1985 and on?

But again, we don't see your full tables, and I agree with YItzhak's comment that it's better if you post CREATE TABLE + INSERT for your tables and data.

Answer 3

A trigger for what you describe is going to be prone to data problems. You would be much better off simply calculating what you are trying to get with a view or a proc. You do not need to store those values.

CREATE VIEW dbo.PurchaseLog AS
SELECT 
       PurchaseDetails.Productid, 
       'IN' AS Movement,
       PurchaseDetails.StockedQty as Quantity,
       PurchaseDetails.PurchaseId as TransactionId, 
       SUM(PurchaseDetails.StockedQty) OVER (PARTITION BY PurchaseDetails.Productid ORDER BY PurchaseDetails.PurchaseId) as Stock
FROM [dbo].[PurchaseDetails];

Answer 4

CREATE TRIGGER [dbo].[TR_PurchaseDetails] ON [dbo].[PurchaseDetails] 
AFTER INSERT
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @ProductId varchar(50);
    DECLARE @CurrentStock bigint;
    DECLARE @Stock bigint;

    SELECT @ProductId  = i.ProductId FROM inserted AS i;

    SELECT TOP 1 @CurrentStock = ISNULL(@Stock, 0) 
    FROM [dbo].[InventoryLogs] 
    WHERE [ProductId] = @ProductId ORDER BY [PurchaseLogId] DESC;

    INSERT INTO [dbo].[InventoryLogs]([ProductId], [Movement], [Quantity], [TransactionId], [Stock])
    SELECT Productid, 'IN', StockedQty, PurchaseId, @CurrentStock + StockedQty
    FROM inserted AS i;
END