CODEWITHSUNDEEP
sqlsql-servertriggerssetwhere-clause
YelizavetaYR
YelizavetaYR

Reputation: 1701

SQL Trigger not working upon update

I have the following code (in sql server - via 2012): i can't seem to get it right. any suggestions.

Table:

select top 1000 [supplier],
                [item],
                [reorder_level],
                [current_inventory],
                [reorder],
from [clinic].[dbo].[emr_suppliers]

I'm working on a trigger and a bit stuck. 

CREATE TRIGGER reorder_supplies
ON emr_suppliers
After insert, update 
As BEGIN

update  emr_suppliers
set reorder = 'yes' 
where (emr_suppliers.reorder = emr_suppliers.current_inventory or emr_suppliers.reorder > emr_suppliers.current_inventory)

update  emr_suppliers
set reorder = 'no' 
where emr_suppliers.reorder < emr_suppliers.current_inventory

END

What the trigger has to do is compare the Current Inventory with the Reorder Level column, and if the value of the Current Inventory is equal to or less than the Reorder Level, it will put a value of Yes in the Reorder column, and if it is not, then it will put a No value instead.

Upvotes: 0

Views: 77

Answers (1)

Lmu92
Lmu92

Reputation: 952

The trigger itself looks syntactically correct. However, I don't think it's a solution with a decent performance since each and every row of the emr_suppliers table is touched twice, even though there was no data change at all for most of the rows (e.g. after insert of a new row or update of a single value).

I'd use a solution based on the internal inserted table together with a CASE expression:

UPDATE  emr_suppliers
SET reorder = 
    CASE WHEN emr_suppliers.reorder <  emr_suppliers.current_inventory THEN 'no'
         WHEN emr_suppliers.reorder >= emr_suppliers.current_inventory THEN 'yes'
    ELSE reorder -- don't change the value
    END
FROM emr_suppliers INNER JOIN inserted ON emr_suppliers.primary_key = inserted.primary_key

Upvotes: 1

Related Questions