Incorrect syntax near a var

Question

I'm working with HeidiSQL on SQL Server, trying to create an after insert trigger, here is the code.

CREATE TRIGGER TR_After_Insert_User ON User1 AFTER INSERT
AS
BEGIN
    declare @userName varchar(24)
    declare @userLName varchar(20)
    declare @userSex varchar(10)

    select @userName = i.name from inserted i
    select @user1LName = i.lastname from inserted i
    select @userSex = i.gender from inserted i

    IF @userSex = 'Male'
       @userSex = 'M'
    ELSE
        @userSex = 'F'

    INSERT INTO db2.dbo.user2(name, lastname, gender) 
    VALUES (@legajoName, @legajoName, @legajoSexo)
END

The error I get:

Incorrect syntax near '@userSex'

I've tried it without the IF sentence, and it worked, so the problem is there. But I need that sentence, because the 'gender' field from second table its 'char' type.

Answer 1

You've missed the SET:

IF @userSex = 'Male'
   SET @userSex = 'M'
ELSE
   SET @userSex = 'F'

In SQL this is invalid syntax for assigning a value to a variable: @userSex = 'M'.

Reference

SET @local_variable

Sets the specified local variable, previously created with the DECLARE @local_variable statement, to the given value.

SET { { @local_variable = expression }