How to create triggers which ensure the column value range and applies to insert and updates?-CodePudding

I have create a triggers but its not working as I want. For example, I want to make sure that the order_limits filed should be between 1-10 and it should applied with insert and updates. But I cannot able to figure out how to make it work with updates and insert. Can anyone help me please? Thanks

CREATE TRIGGER trRestrictRange
ON DATABASE
FOR ALTER_TABLE
AS
BEGIN 
 ALTER TABLE Order
ADD CONSTRAINT const_range CHECK (order_limits >=1 AND order_limits <= 10 );
END

CodePudding user response：

If you "must" use a trigger (there is no good reason why you "must") then you need to use a DML trigger, not a DDL trigger, and check the data in the inserted pseudo table. Then, if the data exists in the table, THROW an error:

CREATE TRIGGER Chktrg_OrderRange ON dbo.YourTable
AFTER INSERT AS
BEGIN
    IF EXISTS (SELECT 1
               FROM inserted i
               WHERE order_limits < 1 
                  OR order_limits > 10)
        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;

Of course, as I stated, there is no good reason to do this, and you should, instead, be using a CHECK CONSTRAINT:

ALTER TABLE dbo.YourTable ADD CONSTRAINT chk_OrderRange
CHECK (order_limits >= 1 AND order_limits <= 10);