SQL Server 2014 – Alternatives for JOIN

sql serversql server 2014

First Trigger

ALTER TRIGGER [dbo].[Price_Modified]
    ON [dbo].[stock_recieve]
    AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE (Stock_out) 
BEGIN
    UPDATE s
    SET value = new.Item_Price * new.Stock_out
    FROM stock_recieve AS s
    JOIN inserted AS new ON new.[Bill No] = s.[Bill No]
    JOIN deleted AS old ON old.[Bill No] = s.[Bill No]
END;

Second Trigger.

 ALTER TRIGGER [dbo].[newval] 
      ON [dbo].[stock_recieve]
        AFTER UPDATE
    AS
    SET NOCOUNT ON;
    IF UPDATE (Stock_out) 
    BEGIN
        UPDATE s
        SET Uservalue = new.Item_Price * new.Stock_out
        FROM stock_recieve AS s
        JOIN inserted AS new ON new.[Bill No] = s.[Bill No]
        JOIN deleted AS old ON old.[Bill No] = s.[Bill No]
    END;

Result to be obtained on first stock_out update.

| Item_Price | Stock_in | Stock_out | Value | Uservalue |
|------------|----------|-----------|-------|-----------|
| 50         | 200      | 100       | 5000  | 5000      |
| 100        | 50       | 20        | 2000  | 2000      |
| 30         | 100      | 40        | 1200  | 1200      |

Result to be obtained on second stock_out update.

| Item_Price | Stock_in | Stock_out | Value | Uservalue |
|------------|----------|-----------|-------|-----------|
| 50         | 200      | 200       | 10000 | 5000      |
| 100        | 50       | 40        | 4000  | 2000      |
| 30         | 100      | 100       | 3000  | 1800      |

The first trigger does what its suppose to do, but I want the second trigger on Uservalue to not add but instead replace the result.
Hope this is clear @Ypercube

Best Answer

It seems you want to base the result on UserValue on the difference between the new and the old value of Stock_Out. Using two triggers for this does not sound like a good idea.

(Using 2 triggers on the same table and action does not sound like a good idea in general, unless the logic is so much complicated that you need to separate it into 2 parts.)

-- Drop all existing UPDATE triggers on the table
DROP TRIGGER [dbo].[Price_Modified] ;
DROP TRIGGER [dbo].[newval] ;

-- trigger that updates UserValue based on the diff (new - old) of Stock_Out
CREATE TRIGGER [dbo].[stockout_modified] 
  ON [dbo].[stock_recieve]
    AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE (Stock_out) 
BEGIN
    UPDATE s
    SET Uservalue = new.Item_Price * (new.Stock_out - old.Stock_out) 
    FROM stock_recieve AS s
    JOIN inserted AS new ON new.[Bill No] = s.[Bill No]
    JOIN deleted AS old ON old.[Bill No] = s.[Bill No]
END;
Related Question