How to check if the id of one table is exists in another table using triggers in SQL Server?

I have created this trigger but when I am trying to insert or update the record this code is not working properly.For example, when I am trying to insert record into order table with customer id thats is not exists in customer table, then its showing me message ‘Yes’ instead of ‘No’. Just need you help to check where I am wrong. Thanks

CREATE TRIGGER trg_checkKey ON ORDER
AFTER INSERT, UPDATE AS
BEGIN
IF EXISTS (
     
     SELECT 1 FROM inserted
     JOIN(
     SELECT ID FROM Order
     UNION ALL
     SELECT ID FROM Customer
     ) Order_Customer
     ON Order_Customer.ID = inserted.ID)
     BEGIN
     PRINT 'Yes';
     END
     ELSE
     BEGIN
     PRINT 'NO'
     END
    -- THROW 98765, N'The Trigger check Chktrg_OrderRange on the table ''dbo.YourTable'' failed. The column ''order_limits'' must be more than or equal to one and less than or equal to 10.', 16;



END

>Solution :

From the comments, it appears that this is another example of trying to get a square peg through a circular hole. Like in your other question, this is not what a TRIGGER is for; CONSTRAINTs are what you need to be using.

You should be using a FOREIGN KEY CONSTRAINT here. Stop abusing TRIGGERs; they aren’t to be used for which they can be solved with in-built features:

ALTER TABLE dbo.[Order] ADD CONSTRAINT FK_OtherTable_YourTable_ID --ORDER is a reserved keyword, don't use it for object names
FOREIGN KEY (ID) REFERENCES dbo.Customer (ID); --If the Customer is the ID, what is the ID for the Order called?

Leave a Reply