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

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 that does not exist in the customer table, then it should show a 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

CodePudding user response：

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?