SQL Triggers not preventing insert

Question

I am having trouble getting a trigger to work. What it needs to do is to prevent any customer with last name Glass from being inserted into the database. This is what I have:

CREATE TRIGGER no_last_name_glass_trg
ON customer
AFTER INSERT,UPDATE
AS
BEGIN
DECLARE @CUSTOMER_LAST VARCHAR;
SET @CUSTOMER_LAST= (SELECT INSERTED.customer_last FROM INSERTED);

  IF @CUSTOMER_LAST = 'Glass'
    BEGIN
    ROLLBACK
    RAISERROR('Applicant Glass Denied',14,1);
  END;
END;

The issue is when I add customers with last name of Glass, it inserts without throwing an error. Any ideas? I can make it work in Oracle, but the same method does not work in MS SQL.

asked 2017-04-10 by user121168

---

Answer

The problem with your code is that you are declaring your variable as varchar without any length. With variable declarations, varchar defaults to a length of 1 (note that in other scenarios it will have a different default length).

As a best practice, you should always specify a length on the varchar datatype. For more details, see this article on the issue.

answered 2017-04-10 by Andy Mallon