IF EXISTS ( SELECT * from Traveler tr JOIN deleted d on tr.fk_product = d.pk) AND EXISTS ( SELECT * from Trans_sernum ts JOIN deleted d on ts.fk_product = d.pk) AND EXISTS ( SELECT * FROM inserted i JOIN DELETED d ON d.pk = i.pk>There was one modification in the way the pk was being checked. A select had to be used to match the deleted.pk. Below is the actual trigger. .
>CREATE TRIGGER CK_Product_update ON [Product] >FOR UPDATE >AS >DECLARE @countFk int, @countTrans int >SELECT @countFk = count(TRAVELER.fk_product) > from Traveler where fk_product IN > (SELECT PK FROM DELETED) > >SELECT @countTrans = count(fk_product) > from Trans_sernum where fk_product IN > (SELECT PK FROM DELETED) > >IF (@countFk > 0 or @countTrans > 0) > and EXISTS ( SELECT * FROM inserted i JOIN > DELETED d ON d.pk = i.pk > WHERE d.fk_engine <> i.fk_engine > OR d.fk_assembly <> i.fk_assembly > OR d.fk_basematerial <> i.fk_basematerial > OR d.fk_parttype <> i.fk_parttype > or d.hpt <> i.hpt > or d.stage <> i.stage) > > BEGIN > PRINT 'Cannot change Products that have dependents' > PRINT 'Transaction has been cancelled' > ROLLBACK > END > >