SQL Trigger Syntax

Hi,

I have this trigger: -
ALTER TRIGGER [dbo].[InvMasterSetOnHoldStatus]
   ON  [dbo].[InvMaster]
   AFTER INSERT
AS 
    UPDATE dbo.InvMaster
       SET StockOnHold = 'F'
         , StockOnHoldReason = 'NEW'    
      WHERE StockCode in (SELECT StockCode FROM Inserted WHERE (WarehouseToUse <> 'R ' AND WarehouseToUse <> 'W '));

Open in new window


I want it to fire when WarehouseToUse  is not R or W.

When I click submit, I just get an hourglass, does anyone know where I'm going wrong?
HKFueyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gplanaCommented:
Try this:

CREATE OR REPLACE TRIGGER [dbo].[InvMasterSetOnHoldStatus]
   AFTER INSERT ON  [dbo].[InvMaster]
AS 
    UPDATE dbo.InvMaster
       SET StockOnHold = 'F'
         , StockOnHoldReason = 'NEW'    
      WHERE StockCode in (SELECT StockCode FROM Inserted WHERE (WarehouseToUse <> 'R ' AND WarehouseToUse <> 'W '));
END
GO

Open in new window

0
HKFueyAuthor Commented:
Hi gplana Result is this: -
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'OR'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'END'.
0
HuaMin ChenProblem resolverCommented:
Try
ALTER TRIGGER [dbo].[InvMasterSetOnHoldStatus]
   ON  [dbo].[InvMaster]
   AFTER INSERT
AS 
begin
declare @WarehouseToUse varchar(5),
	@StockCode varchar(20)
    select @WarehouseToUse=WarehouseToUse,
    	@StockCode=StockCode from Inserted
    if @WarehouseToUse not in('R','W')
    begin transaction;
	    UPDATE dbo.InvMaster
	       SET StockOnHold = 'F'
		 , StockOnHoldReason = 'NEW'    
	    where StockCode=@StockCode;
    COMMIT TRANSACTION;
end

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

HKFueyAuthor Commented:
I have managed to get my syntax to work, I will test and report.
0
Scott PletcherSenior DBACommented:
The syntax is not the big problem.  You need to make sure you don't update too many rows in the original table because you're matching only by the Warehouse rather than by the key column(s) of the table.

You'll need to replace "<key_col>" (and <key_col2> ... if applicable) with the appropriate unique column values, such as "item_number" (hopefully) or "id" or whatever the key column happens to be for the InvMaster table.


ALTER TRIGGER [dbo].[InvMasterSetOnHoldStatus]
   ON  [dbo].[InvMaster]
   AFTER INSERT
AS
    SET NOCOUNT ON
    UPDATE im
       SET StockOnHold = 'F'
         , StockOnHoldReason = 'NEW'
    FROM inserted i
    INNER JOIN dbo.InvMaster im ON
        im.<key_col> = i.<key_col> --and im.<key_col2> = i.<key_col2>
    WHERE
        i.WarehouseToUse NOT IN ('R', 'W')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HKFueyAuthor Commented:
Thanks for the help on this.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.