CODEWITHSUNDEEP
sql-servert-sqltriggers
Escaper
Escaper

Reputation: 69

Can't get out of if statement which is in TSQL : trigger

This is my trigger which is supposed to prevent the user to inter "@" , "$" or "_" in City . If has two condition and it works perfectly 

ALTER TRIGGER i_validation
ON dbo.Customer
FOR INSERT, UPDATE
AS
    DECLARE @i_not NVARCHAR(200)
begin
    SELECT @i_not = dbo.Customer.City 
    FROM dbo.Customer 
    JOIN inserted ON dbo.Customer.CustomerID = inserted.CustomerID

    IF((@i_not LIKE '%@%') OR (@i_not LIKE '%$%'))
    BEGIN
        THROW 50000, 'First name cannot contain: "@" ,"$" ,or "_"', 1;
    END;
END

But when I add the third condition : 

1Version.IF(( @i_not LIKE '%@%') OR ( @i_not LIKE '%$%') or @i_not LIKE '%_%' ) 
2Version. IF((( @i_not LIKE '%@%') OR ( @i_not LIKE '%$%')) OR @i_not LIKE '%_%' )

I also tried second if additional else if 

ALTER TRIGGER i_validation
ON dbo.Customer
FOR INSERT, UPDATE
AS
    DECLARE @i_not NVARCHAR(200)
BEGIN
    SELECT @i_not = dbo.Customer.City 
    FROM dbo.Customer 
    JOIN inserted ON dbo.Customer.CustomerID = inserted.CustomerID

    IF ((@i_not LIKE '%@%') OR (@i_not LIKE '%$%'))
    BEGIN
        THROW 50000, 'First name cannot contain: "@" ,"$" ,or "_"', 1;
    END;
    ELSE IF (@i_not LIKE '%_%')
    BEGIN
        THROW 50000, 'First name cannot contain: "@" ,"$" ,or "_"', 1;
    END
END

But result, it whether ignores or works every time even though city does not contain @ , $, _ .

Are there any other ways to use if with 3 conditions?

Upvotes: 1

Views: 40

Answers (1)

Andomar
Andomar

Reputation: 238086

The reason your checks fail is that underscore is a special character when you use it with LIKE. It matches any single character, just like % matches any text.

But why not use a check constraint?

ALTER TABLE Customer ADD CONSTRAINT check1 CHECK (City not like '%[@$_]%');

Here [@$_] is a character range matching either @, $ or _. An underscore has no special meaning in a character range.

Upvotes: 6

Related Questions