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.

score 6 · Accepted Answer · edited Jun 20 '20 at 09:12

6

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 }

edited Jun 20 '20 at 09:12

Community

1
1

answered Sep 18 '15 at 14:54

Tanner

22,205
9
65
83

score 2 · Answer 2 · answered Sep 18 '15 at 15:14

Your trigger has a major logical flaw. It assumes there will only ever be a single row inserted. This is a common but major problem. In sql server triggers fire once per operation, not once per row. You can change your whole trigger to be a single set based insert and drop all those scalar variables.

CREATE TRIGGER TR_After_Insert_User ON User1 AFTER INSERT
AS
BEGIN
    INSERT INTO db2.dbo.user2(name,lastname,gender) 
    select name, lastname, left(gender, 1) --or you could use a case expression here
    from inserted
END

Incorrect syntax near a var

2 Answers2

SET @local_variable