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
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
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?