Update Trigger (table joining) - I'm almost there, please help!! 1

[JulesDBQ]

I am writing an update trigger that will update a value in database #2 whenever a certain column in database #1 is updated. The trigger is firing correctly and does update the field that it is supposed to. The problem is that I don't know how to join the Inserted table from database #1 into this trigger. I have joined the table from database #2 with the delete table from database #1, but I need to also join the inserted table. This is what I have so far...

CREATE TRIGGER UPDT_HW_MACHINEID
ON [dbo].[Hardware]
FOR UPDATE
AS
IF UPDATE(MachineID)
UPDATE b
SET MachineID = 'Test'
FROM [SoftwareDB].[dbo].[Licensed_PCs] b JOIN Deleted d on b.MachineID = d.MachineID
GO

This code does update the appropriate record to the word Test, but I want it to update the value to the Inserted value from database #1, so....

Set MachineID = i.MachineID

How do I join the Inserted table to the join I already have listed above. Any help that you can offer would be most appreciated. Thanks very very much

 

[swampBoogie]

What other columns are there in the Hardware table? You need some unique value (that is not updated) in order to join the records.

 

[JulesDBQ]

Thanks for getting back to me. Unfortunately, the MachineID is the only unique value in the Hardware table. What else would you suggest?

 

[JulesDBQ]

Wait a second, I did think of a field that I can use and it works!!! Thank you very very very very much. I have been working on this trigger off and on for quite some time. It totally makes sense now!! [SMILE]

 

[swampBoogie]

As far as I can see there is no solution with your current data model as you can't match the rows in the inserted and deleted tables. It would work if at most one row is updated but that seems to large a restriction.

Adding an identity column to the hardware table seems the easiest solution.

What you need is a row trigger but that is not supported by SQL server.