I have 2 tables. If I delete a record from table1 then first query should check if it's pk exists as a foreign key in table2 then it should not delete the record else it should.
I used this but throwing syntax error
DELETE FROM Setup.IncentivesDetail
INNER JOIN Employee.IncentivesDetail ON Setup.IncentivesDetail.IncentivesDetailID = Employee.IncentivesDetail.IncentiveDetail_ID
WHERE Setup.IncentivesDetail.IncentivesDetailID= @IncentivesDetailID
AND Employee.IncentivesDetail.IncentiveDetail_ID= @IncentivesDetailID
UPDATE:
Based on the answers below I have done this, is it correct ?
If Not Exists(Select * from Employee.IncentivesDetail where IncentivesDetail.IncentiveDetail_ID= @IncentivesDetailID)
Begin
Delete from Setup.IncentivesDetail
WHERE Setup.IncentivesDetail.IncentivesDetailID= @IncentivesDetailID
End
Else
Begin
RAISERROR('Record cannot be deleted because assigned to an employee',16,1)
RETURN
End