question

ShaunBailey-5325 avatar image
0 Votes"
ShaunBailey-5325 asked ErlandSommarskog answered

SQL Trigger for Lookup

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!





sql-server-generalazure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ShaunBailey-5325 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @ErlandSommarskog for the quick reply!

The reason I thought I would need a trigger is that I want it to run anytime I add data to that table. With that in mind, would I need to do that through a trigger or is there some other way that I am unaware of.

Thanks in advance!

0 Votes 0 ·