Trying to update last inserted record with For Insert trigger 1

[xcaliber2222]

Hello, I am trying to update only the most recently inserted record. I thought selecting the MAX id value in the WHERE clause would do the trick, but this keeps updating all the records in the table, instead of the one that was just inserted. Soryy if this seems like a basic question, but if someone could show me where I'm going wrong I would really appreciate it.

Thanks, Alejandro

ALTER TRIGGER primaryVendorFlagUpdate ON [Warranty_Registration]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON

UPDATE Warranty_Registration
SET PRIMARY_VENDOR = a.PRIMARY_VENDOR
FROM dbo.AEO_DEVL_SEQFLEXT a INNER JOIN inserted i ON
a.ENG_SERIAL_NUM = i.Engine_Serial_No AND
a.ENG_MODEL_CD = i.Engine_Model_Code
WHERE Warranty_Registration.Warranty_Reg_ID = (select MAX (Warranty_Registration.Warranty_Reg_ID) from Warranty_Registration)

SET NOCOUNT OFF
END

 

[SajidAttar]

Hi..

Try something like below..

UPDATE A
Set a.Primary_Vendor = c.Primary_Vendor
From Warranty_Registration A
Inner Join Inserted B On a.Warranty_Reg_Id = b.Warranty_Reg_Id 
Inner Join AEO_DEVL_SEQFLEXT C on b.Engine_Serial_No = c.ENG_SERIAL_NUM 
    b..Engine_Model_Code = c.ENG_MODEL_CD

**This is not tested.. if you get any error do fix and run it.

HTH.

Regards,


"Dream not what makes your sleep a pleasure, but what makes you work
hard to achieve it and forget your sleep (untill you achieve it)." -- SJD

 

[xcaliber2222]

Thanks SajidAttar!

I'll run it and let you know how it goes.

Regards,
Alejandro

 

[xcaliber2222]

Hello Sajid,

Yes, this ran great. Thank you so much for your help. This works perfectly. One quick question please. If I add a WHERE clause like I have here, will this take care of a null condition? I'm trying to make sure that if there isn't a matching Engine_Serial_No or Engine_Model_Code in the AEO_DEVL_SEQFLEXT table there is no error and a default value of 'TEST' is set for the Primary_Vendor. Not quite sure what the best way would be to set the value though. Could I just set a default value on the table column in design view? That should be OK I would think, but I'm not 100% sure. Anyway, any advice is always appreciated.

UPDATE A
Set a.Primary_Vendor = c.Primary_Vendor
From Warranty_Registration A
Inner Join Inserted B On a.Warranty_Reg_Id = b.Warranty_Reg_Id 
Inner Join AEO_DEVL_SEQFLEXT C on b.Engine_Serial_No = c.ENG_SERIAL_NUM 
    b.Engine_Model_Code = c.ENG_MODEL_CD 
WHERE c.ENG_SERIAL_NUM or c.ENG_MODEL_CD IS NOT NULL

Thanks,
Alejandro