Need help with my trigger

1

USE [cT]
GO
/****** Object: Trigger [dbo].[testinsert] Script Date: 5/4/2018 12:13:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[testinsert] ON [dbo].[InvMovements]
/** for iserted lines only **/
AFTER insert
AS BEGIN
declare @TrnType char(1)
declare @Source char(1)
declare @MovementType char(1)

 /** check if transaction is originated from PO **/
   SELECT @TrnType = inserted.TrnType, @Source = inserted.Source, @MovementType = inserted.MovementType from inserted

		if ((@TrnType = 'R') and (@Source = 'P') and (@MovementType = 'I'))
                 Here I need to check the entry in lot detail table.
	
            Begin
	 declare @RandomNumber VARCHAR(4) =
              RIGHT('0000'+CONVERT(varchar(4),convert(int,rand()*10000)),4)
			  UPDATE dbo.InvMovements
		
			  SET LotSerial = dbo.InvMovements.LotSerial + '.' + @RandomNumber
			  FROM inserted
			  WHERE 
					 dbo.InvMovements.StockCode = inserted.StockCode and 
					 dbo.InvMovements.Warehouse = inserted.Warehouse and
					 dbo.InvMovements.TrnYear = inserted.TrnYear and 
					 dbo.InvMovements.TrnMonth = inserted.TrnMonth and
					 dbo.InvMovements.EntryDate = inserted.EntryDate and
					 dbo.InvMovements.LotSerial = inserted.LotSerial and
					 dbo.InvMovements.Bin = inserted.Bin

			   UPDATE dbo.LotTransactions
			   /** append this number to existing lot number following a period **/
			   SET Lot = dbo.LotTransactions.Lot + '.' + @RandomNumber
			   FROM inserted
			   WHERE 
					 dbo.LotTransactions.StockCode = inserted.StockCode and 
					 dbo.LotTransactions.Warehouse = inserted.Warehouse and
					 dbo.LotTransactions.JnlYear = inserted.TrnYear and
					 dbo.LotTransactions.JnlMonth = inserted.TrnMonth and
					 dbo.LotTransactions.Lot = inserted.LotSerial and
					 dbo.LotTransactions.Bin = inserted.Bin and
					 dbo.LotTransactions.JobPurchOrder = inserted.Job and
					 dbo.LotTransactions.Journal = inserted.Journal and
					 dbo.LotTransactions.JournalLine = inserted.JournalEntry

		END


END

Before I perform any updates in Inventory Movement I need to check a record in Lot Transaction Table.
dbo.LotTransactions.StockCode = inserted.StockCode and
dbo.LotTransactions.Warehouse = inserted.Warehouse and
dbo.LotTransactions.JnlYear = inserted.TrnYear and
dbo.LotTransactions.JnlMonth = inserted.TrnMonth and
dbo.LotTransactions.Lot = inserted.LotSerial and
dbo.LotTransactions.Bin = inserted.Bin and
dbo.LotTransactions.JobPurchOrder = inserted.Job and
dbo.LotTransactions.Journal = inserted.Journal and
dbo.LotTransactions.JournalLine = inserted.JournalEntry and dbo.LotTransaction.Line = 1

If the line is 1 I can proceed and follow with my code to update InvMovements and Lot Transaction Table.
How can I check for this first?

Thanks

2

Please post the create table scripts for all tables involved and what you want to accomplish in your trigger with those tables and under what conditions.

3

TABLE 1
CREATE TABLE [dbo].[InvMovements](
[StockCode] varchar NOT NULL,
[Warehouse] varchar NOT NULL,
[TrnYear] [decimal](4, 0) NOT NULL,
[TrnMonth] [decimal](2, 0) NOT NULL,
[EntryDate] [datetime] NOT NULL,
[TrnTime] [decimal](8, 0) NOT NULL,
[MovementType] char NOT NULL,
[Source] char NOT NULL,
[Job] varchar NOT NULL,
[Journal] [decimal](10, 0) NOT NULL,
[JournalEntry] [decimal](10, 0) NOT NULL,
[TrnType] char NOT NULL,
[Bin] varchar NOT NULL,
[GlCode] varchar NOT NULL,
[Salesperson] varchar NOT NULL,
[LotSerial] varchar NOT NULL

CONSTRAINT [InvMovementsKey] PRIMARY KEY CLUSTERED
(
[StockCode] ASC,
[Warehouse] ASC,
[TrnYear] ASC,
[TrnMonth] ASC,
[EntryDate] ASC,
[TrnTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Table 2
CREATE TABLE [dbo].[LotTransactions](
[StockCode] varchar NOT NULL,
[Warehouse] varchar NOT NULL,
[Lot] varchar NOT NULL,
[Bin] varchar NOT NULL,
[TrnDate] [datetime] NOT NULL,
[Line] [decimal](10, 0) NOT NULL,
[TrnType] char NOT NULL,
[TrnQuantity] [decimal](18, 6) NOT NULL,
[TrnValue] [decimal](14, 2) NOT NULL,
[Job] varchar NOT NULL,
[Source] char NOT NULL,
[JobPurchOrder] varchar NOT NULL,
[PurchaseOrderLin] [decimal](4, 0) NOT NULL,
[Supplier] varchar NOT NULL,
[JnlYear] [decimal](4, 0) NOT NULL,
[JnlMonth] [decimal](2, 0) NOT NULL,
[Journal] [decimal](10, 0) NOT NULL,
[JournalLine] [decimal](10, 0) NOT NULL,
[TimeStamp] [timestamp] NULL,
CONSTRAINT [LotTransactionsKey] PRIMARY KEY CLUSTERED
(
[StockCode] ASC,
[Warehouse] ASC,
[Lot] ASC,
[Bin] ASC,
[TrnDate] ASC,
[Line] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

I would require a trigger after InvMovements has a new record entry for TrnType = R, MovementyType = I and Source P

If this is OK next I need to check the record in Lot Taransaction table comparing
StockCode, Lot, Bin, TrnYear = JnlYear, TrnMonth= JnlMonth, Journal = Journal , Journal Entry = JournalLine If all of this found I need to check if the Line filed in LotTransaction Table is 1 if Yes, I need to follow with the update.

4

In your trigger use set base queries using inserted and deleted. avoid using variable which will fail on multi-row inserts.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[testinsert] ON [dbo].[InvMovements]
AFTER insert
AS BEGIN
   UPDATE dbo.InvMovements
   SET LotSerial = dbo.InvMovements.LotSerial + '.' +  RIGHT('0000'+CONVERT(varchar(4),convert(int,rand()*10000)),4)
   FROM inserted i
   JOIN dbo.InvMovements im ON
		   im.StockCode = i.StockCode and 
		   im.Warehouse = i.Warehouse and
		   im.TrnYear = i.TrnYear and 
		   im.TrnMonth = i.TrnMonth and
		   im.EntryDate = i.EntryDate and
		   im.LotSerial = i.LotSerial and
		   im.Bin = i.Bin
   WHERE i.TrnYtpe='R'
   AND   i.Source='P'
   AND   i.MovementType='I';

   UPDATE dbo.LotTransactions
   /** append this number to existing lot number following a period **/
   SET Lot = dbo.LotTransactions.Lot + '.' + RIGHT('0000'+CONVERT(varchar(4),convert(int,rand()*10000)),4)
   FROM inserted
   JOIN dbo.LotTransactions lt ON
		   lt.StockCode = inserted.StockCode and 
		   lt.Warehouse = inserted.Warehouse and
		   lt.JnlYear = inserted.TrnYear and
		   lt.JnlMonth = inserted.TrnMonth and
		   lt.Lot = inserted.LotSerial and
		   lt.Bin = inserted.Bin and
		   lt.JobPurchOrder = inserted.Job and
		   lt.Journal = inserted.Journal and
		   lt.JournalLine = inserted.JournalEntry
   WHERE i.TrnYtpe='R'
   AND   i.Source='P'
   AND   i.MovementType='I';
END
5

This is great, however there is a field in Lot Transaction Table Line that I need to figure out first.
after insert into InvMovements I need to check if the same record exists in LotTransaction exists and the Line is 1.
If Yes I should go ahead with my updates otherwise not updates at all.

6

Add the table to the join and where clauses