1

I want to make a trigger where I can check if the value from the stock of a product 0 is. Then the trigger should make the value 1.

My Trigger

CREATE TRIGGER [IfStockIsNull]
ON [dbo].[Products]
FOR DELETE, INSERT, UPDATE
AS
BEGIN

if [Products].Stock = 0
    UPDATE [Products] SET [Stock] = 1
    FROM [Products] AS m
    INNER JOIN inserted AS i
    ON m.ProductId = i.ProductId;
ELSE
    raiserror('Niks aan het handje',10,16);
END

I'm getting the error:

Altering [dbo].[IfStockIsNull]...
(53,1): SQL72014: .Net SqlClient Data Provider: Msg 4104, Level 16, State 1, 
Procedure IfStockIsNull, Line 7 The multi-part identifier "Products.Stock" 
could not be bound.
(47,0): SQL72045: Script execution error.  The executed script:
ALTER TRIGGER [IfStockIsNull]
ON [dbo].[Products]
FOR DELETE, INSERT, UPDATE
AS BEGIN
       IF [Products].Stock = 0
           UPDATE [Products]
           SET    [Stock] = 1
           FROM   [Products] AS m
                  INNER JOIN
                  inserted AS i
                  ON m.ProductId = i.ProductId;
       ELSE
           RAISERROR ('Niks aan het handje', 10, 16);
   END

An error occurred while the batch was being executed.

Maybe you guys can help me out?

Improve this question
5
  • Which dbms are you using? (That code is product specific.) Commented Jun 7, 2018 at 10:21
  • Looking at the code it seems to be SQL Server. Please add that tag to the question if I'm correct. Commented Jun 7, 2018 at 10:22
  • @jarlh I'm using .NET Framework Data Provider for SQL Server Commented Jun 7, 2018 at 10:22
  • It should be m.Stock. You need to write your alias infront of the columns since you have two identical product columns from two tables. Then it doesnt know which one it should use. Therefore you must put your alias infront. M.Stock - And write UPDATE M Commented Jun 7, 2018 at 10:26
  • I think your problem is on the line if [Products].Stock = 0 (first line in after BEGIN) but I cannot suggest a fix without a bit more context on how/why this trigger needs to run. Should the Trigger alter the Stock value of what is Inserted/Updated ? Should it be looking at all rows in the table? Commented Jun 7, 2018 at 10:27

2 Answers 2

Reset to default
3

A number of issues and surprises in what you're trying to run here. But basically, don't try to run procedural steps when you're dealing with sets. inserted can contain 0, 1 or multiple rows, so which row's stock are you asking about in your IF?

Better to deal with it in the WHERE clause:

CREATE TRIGGER [IfStockIsNull]
ON [dbo].[Products]
FOR INSERT, UPDATE --Removed DELETE, because ?!?
AS
BEGIN

    UPDATE [Products] SET [Stock] = 1
    FROM [Products] AS m
    INNER JOIN inserted AS i
    ON m.ProductId = i.ProductId;
    WHERE m.Stock = 0
    --Not sure what the error is for - the above update may have updated
    --some number of rows, between 0 and the number in inserted.
    --What circumstances should produce an error then?
END

Simple demo script that the UPDATE is correctly targetting only matching rows from inserted:

declare @t table (ID int not null, Val int not null)
insert into @t(ID,Val) values (1,1),(2,2),(3,3)
update
    @t
set
    Val = 4
from
    @t t
        inner join
    (select 2 as c) n
        on
            t.ID = n.c

select * from @t

Shows that only the row with ID of 2 is updated.

Even Microsoft's own example of UPDATE ... FROM syntax uses the UPDATE <table> ... FROM <table> <alias> ... syntax that Gordon asserts doesn't work:

USE AdventureWorks2012;  
GO  
UPDATE Sales.SalesPerson  
SET SalesYTD = SalesYTD + SubTotal  
FROM Sales.SalesPerson AS sp  
JOIN Sales.SalesOrderHeader AS so  
    ON sp.BusinessEntityID = so.SalesPersonID  
    AND so.OrderDate = (SELECT MAX(OrderDate)  
                        FROM Sales.SalesOrderHeader  
                        WHERE SalesPersonID = sp.BusinessEntityID);  
GO

This example does have an issue which is further explained below it but that relates to the "multiple matching rows in other tables will result in only a single update" flaw that anyone working with UPDATE ... FROM should make themselves aware of.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I have misinterpreted that documentation for a long time, but it does behave as you describe.
@GordonLinoff -we've tidied up most of the mess now. I just hope that we can both recognise that we have expertise in this area (we're both in the top 250 worldwide users on SO and both extensively work on SQL Server questions). When you told me I was wrong, I went away and proved to myself that my assertions were correct before coming back (because I respect your input). I'd hope you'd extend me the same courtesy in future.
. . I still find it hard to believe that SQL Server ignores the alias in this case. That behavior seems profane to me (willingly ignoring what the query writer writes) . . .and that has nothing, nothing to do with you.
1

If you want to stop the insert if any rows are bad, then do that before making the changes:

ALTER TRIGGER [IfStockIsNull] ON [dbo].[Products]
    FOR INSERT, UPDATE  -- DELETE is not really appropriate
AS BEGIN
   IF (EXISTS (SELECT 1
               FROM Products p JOIN
                    inserted i
                    ON m.ProductId = i.ProductId
               WHERE p.Stock = 0
              )
      )
    BEGIN
        RAISERROR ('Niks aan het handje', 10, 16);
    END;
     UPDATE p
         SET Stock = 1
         FROM Products p INNER JOIN
              inserted AS i
              ON p.ProductId = i.ProductId;
END;

Notes:

  • The comparison in the IF checks if any of the Product rows have a 0. If so, the entire transaction is rolled back.
  • You might complain that you cannot reject a single row. But that is how transactions work. If any row fails in an INSERT, the entire INSERT is rolled back, not just a single row.
  • The UPDATE is not correct, because you have Products in the FROM clause and the UPDATE clause. You need to use the alias for the first one.
Improve this answer

3 Comments

The UPDATE is fine - "If the object being updated is the same as the object in the FROM clause and there is only one reference to the object in the FROM clause, an object alias may or may not be specified"
@Damien_The_Unbeliever . . . In the OP's code, the reference to Product in the FROM clause has the alias of m. The code does not do what the OP intends.
I have added database(dacpac) project of my .netCore api, and after that I want to publish that dacpac file to azure database by using CI/CD pipeline,dacpac file is created by CI, and release as well, but throwing error: *** Could not deploy package. Error SQL72014: .Net SqlClient Data Provider: Msg 40515, Level 15, State 1, Procedure ddltrg_CREATE_Activity_LOG, Line 16 Reference to database and/or server name in 'TrackDBChanges..tblDDLEventLog' is not supported in this v ersion of SQL Server. Error SQL72045: Script execution error. T

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.