SQL Trigger for Lookup

Question

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!

Answer 1

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.

Answer 2

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.