SQL Trigger for Lookup

Shaun Bailey 86 Reputation points
2022-05-18T21:34:53.033+00:00

Hi there!

I have a table (let's call it 'Order') that references an 'Id' from another table (which we can call 'Menu'). What I need to do is to pull in the id from the menu based on the Order..ItemName = Menu..ItemName.

From what I can gather, I need a trigger to perform this action, however I only want to perform this lookup if the Order..Id IS NULL.

I'm a little stuck and could use some help!

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,705 questions
0 comments
Accepted answer
  1. Erland Sommarskog 100.9K Reputation points MVP
    2022-05-18T21:53:13.3+00:00

    Well, you only said "pull". My experience is that when people use that word, they are looking into selecting data from the database. But I'm not a native speaker of English, so I may have it wrong.

    A trigger for the task would look like this: 

    CREATE TRIGGER FillInTheId ON dbo.Order AFTER INSERT AS

UPDATE Order
SET    id = m.id
FROM    Order O
JOIN    inserted i ON O.<primarykey> = i.<priimarykey>
JOIN    Menu M ON O.ItemName = M.ItemName
WHERE   i.id IS NULL

    The table inserted holds the rows that were inserted by the INSERT statement that fired the trigger. Keep in mind that a trigger fires one per statement.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2022-05-18T21:41:41.463+00:00

    Not sure why you think you need a trigger.

    This is a possible solution: 

    isnull(Order.ID, (SELECT Menu.id FROM Menu.ItemName = Order.ItemName))

    Whether this solution makes is a little hard to say, since I know nothing more about these tables than you told us.

    Beware that if Menu.ItemName is not unique, this will end in tears.