Is there a better way to do this

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Is there a better way to do this

Author

Topic

jibarra
Starting Member

3 Posts

Posted - 2011-01-18 : 23:37:10

Hi, New to SQL. The below Code does what I want but just want to know if there is a better way to get the same results.
When there is an Insert or update on the Sale PackingSlip Table Some Data has to be copied to another table (ItemTransations)
There will be more than one line of date in an Insert or update.

Ta
Jorge


ALTER TRIGGER [OMS].[UpdateSalePackingSlip] 
   ON  [OMS].[SalePackingSlip]
   AFTER INSERT,UPDATE
AS 
BEGIN
SET NOCOUNT ON;
	
IF Update(Quantity) or Update(LocationID) or Update(ItemID)
    begin
	 
	 Declare @TempTable Table ( 
		PK int identity(1,1) not null Primary Key,
		Refance Int,
		LocationID Int,
		Qty Numeric(9,0),
		ItemID int)
	 
	 Declare @MaxRecords int,
			 @RecordCount int
	 
	 Insert Into @TempTable (ItemID, Refance, LocationID,Qty)
					  Select ItemID, ItemSaleLineID, LocationID, Quantity
					  From Inserted
			
			Set @MaxRecords = (Select Max(PK) From @TempTable)
			Set @RecordCount = 1
	Declare
			@ItemID Int,
			@Refance Int,
			@LocationID Int,
			@Qty Numeric(9,0)
				
	 While @RecordCount <= @MaxRecords
		begin 
		Select @Refance = Refance,
			   @LocationID = LocationID,
			   @Qty = Qty,
			   @ItemID = ItemID 
		From @TempTable 
		Where PK = @RecordCount
		
			If Exists (Select RefanceID,ItemID,LocationID 
						From IMS.ItemTransactions
						Where RefanceID = @Refance and ItemTransactionTypeID = 1)
					    
					    Begin
						 Update IMS.ItemTransactions
						 Set
						 ItemID = @ItemID,
						 Quantity = @Qty*-1,
						 LocationID = @LocationID
						 Where RefanceID = @Refance and ItemTransactionTypeID = 1
						end
				 Else
					    Begin
						 Insert Into IMS.ItemTransactions(
						 ItemID, 
						 RefanceID,
						 ItemTransactionTypeID,
						 LocationID,
						 Quantity)
						Values(
						 @ItemID,
						 @Refance,
						 1,
						 @LocationID,
						 @Qty*-1)
						end
						
			Set @RecordCount = @RecordCount + 1	
		 end	
	 end
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-18 : 23:54:59

replace the trigger body with this


	Insert Into IMS.ItemTransactions( ItemID, RefanceID, ItemTransactionTypeID, LocationID, Quantity)
	Select	i.ItemID, i.ItemSaleLineID, 1, i.LocationID, i.Qty*-1
	from	inserted i
	where	not exists
		(
			select	*
			from	IMS.ItemTransactions x
			where	x.RefanceID		= i.RefanceID
			and	x.ItemTransactionTypeID	= 1
		)
	
	Update	t
	Set	ItemID 		= i.ItemID,
		Quantity 	= i.Qty*-1,
		LocationID 	= i.LocationID
	From	IMS.ItemTransactions t
		inner join inserted i	on	t.RefanceID	= i.ItemSaleLineID
	where	t.ItemTransactionTypeID	= 1

KH
[spoiler]Time is always against us[/spoiler]

jibarra
Starting Member

3 Posts

Posted - 2011-01-19 : 01:28:20

Thanks khtan !!


ALTER TRIGGER [OMS].[UpdateSalePackingSlip] 
   ON  [OMS].[SalePackingSlip]
   AFTER INSERT,UPDATE
AS 
BEGIN
SET NOCOUNT ON;
	
Insert Into IMS.ItemTransactions( 
								 ItemID, 
								 RefanceID, 
								 ItemTransactionTypeID, 
								 LocationID, 
								 Quantity
								 )
	Select	i.ItemID, i.ItemSaleLineID, 1, i.LocationID, i.Quantity*-1
	from	inserted As i
	where	not exists
					  (
					   select	*
					   from	IMS.ItemTransactions as x
					   where x.RefanceID = i.ItemSaleLineID
					   and	x.ItemTransactionTypeID	= 1
					  )
	
	Update	t
	Set	ItemID 		= i.ItemID,
		Quantity 	= i.Quantity*-1,
		LocationID 	= i.LocationID
		
	From IMS.ItemTransactions As t
		 Inner join inserted As i	
		 on	t.RefanceID	= i.ItemSaleLineID
		where t.ItemTransactionTypeID = 1




END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources