none
Syntax error declaring variables within trigger

    Question

  • I'm writing a trigger that when a new record is added to one database (basically a new customer being added), it then adds some of those details to a second database which is used by another internal system.

    I've come up with the following code :

    create trigger NewEntity
    on [Intranet-Test].[dbo].[vpw_Entities]
    after insert
    as
    	declare @type_id int
    	declare @entity_id int
    	declare @Is_Active bit
    	declare @Entity_Name nvarchar(256)
    	@Is_Active=inserted.IsActive
    	@entity_id=inserted.EntityID
    	@Entity_Name=inserted.EntityName
    	@type_id=inserted.TypeID
    
    	if @type_id in ('2','3')
    	begin
    		insert into [TimeTracking-Test].[dbo].[clients] (clientid, active, clientname)
    		values (@entity_id, CAST(@Is_Active as INT), @Entity_Name)
    	end
    go

    but within SSMS it's indicating a syntax error near the "@Is_Active=inserted.IsActive" line, which appears to be related to the declare statements (eg if I add anything between them the new line gets the syntax error).

    Looking online I can't find any indication of why I'd be getting this error, though I'm assuming that variable declaration is actually allowed within a trigger.

    Anyone know why this isn't working? If it is the declare statements at fault, do you have any ideas how to achieve the same thing without them? I tried putting the inserted.entityname etc references directly into the values part of the insert statement but that also gave me an error.

    Thursday, August 07, 2014 4:38 PM

Answers

  • Hi keith,

    inserted is magic table. So we have to query against the table. I am not sure that whether we can directly access like inserted.<<columname>> or not.

    I have done the below changes in your query and working fine. Pls check.

    create trigger NewEntity
    on [Intranet-Test].[dbo].[vpw_Entities]
    after insert
    as
    	declare @type_id int
    	declare @entity_id int
    	declare @Is_Active bit
    	declare @Entity_Name nvarchar(256)
    	SELECT @Is_Active=IsActive,
    	@entity_id=EntityID,
    	@Entity_Name=EntityName,
    	@type_id=TypeID
    	FROM inserted
    	if @type_id in ('2','3')
    	begin
    		insert into [TimeTracking-Test].[dbo].[clients] (clientid, active, clientname)
    		values (@entity_id, CAST(@Is_Active as INT), @Entity_Name)
    	end
    go
    Note: Trigger may return multiple records when multiple rows updated by a DML. So better use direct select statetemt instead of storing in variable.

    -Pls mark as answer/vote, if this post is helpful.

    Sugumar Pannerselvam.


    Thursday, August 07, 2014 5:01 PM
  • Btw, you can simply insert the result of the inserted table. Also, Inserted is a table and using scalar values will have the trigger missing a lot of data.

            insert into [TimeTracking-Test].[dbo].[clients] (clientid, active, clientname)
            select EntityID, IsActive, EntityName
            from inserted
            when TypeID in ('2','3')

    Thursday, August 07, 2014 8:43 PM

All replies

  • Hello Keith,

    If you want to use one DECLARE for several variable, then you have to seperate the variable with a comma.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, August 07, 2014 4:53 PM
  • Hi keith,

    inserted is magic table. So we have to query against the table. I am not sure that whether we can directly access like inserted.<<columname>> or not.

    I have done the below changes in your query and working fine. Pls check.

    create trigger NewEntity
    on [Intranet-Test].[dbo].[vpw_Entities]
    after insert
    as
    	declare @type_id int
    	declare @entity_id int
    	declare @Is_Active bit
    	declare @Entity_Name nvarchar(256)
    	SELECT @Is_Active=IsActive,
    	@entity_id=EntityID,
    	@Entity_Name=EntityName,
    	@type_id=TypeID
    	FROM inserted
    	if @type_id in ('2','3')
    	begin
    		insert into [TimeTracking-Test].[dbo].[clients] (clientid, active, clientname)
    		values (@entity_id, CAST(@Is_Active as INT), @Entity_Name)
    	end
    go
    Note: Trigger may return multiple records when multiple rows updated by a DML. So better use direct select statetemt instead of storing in variable.

    -Pls mark as answer/vote, if this post is helpful.

    Sugumar Pannerselvam.


    Thursday, August 07, 2014 5:01 PM
  • You also want to "set" variables to values - i.e., "set @Is_Active = 1;"

    Writing trigger code is not something for the inexperienced - you have already fallen for one of the most common misunderstandings.  A trigger executes once for the associated statement - that statement can affect zero, one, or many rows.  Do not assume that there is only 1 row affected by the insert (in this case) statement. A better approach to your logic is:

    if exists (select * from inserted where TypeID in ('2', '3'))
    insert [TimeTracking-Test].dbo.clients (client_id, active, clientname)
    select EntityID, IsActive, EntityName from inserted where TypeID in ('2', '3');

    Thursday, August 07, 2014 5:04 PM
  • Thanks everyone for the quick replies, and thanks especially to Sugumar, that fixed it for me.

    Olaf: I thought that was only if you had a single declare statement on one line, eg :
    declare @foo int, @bar int, etc
    though I did previously have it listed like that (tried various things in an attempt to resolve before posting).

    Scott & Sugumar: re: multiple rows, yeah I had intended to clarify that I was already aware of that. Fortunately the nature of the source database is such that it's impossible for there to be multiple additions in a single statement, which is why I'm not worried about that. Thanks for pointing it out just in case though.

    Thursday, August 07, 2014 6:16 PM
  • Btw, you can simply insert the result of the inserted table. Also, Inserted is a table and using scalar values will have the trigger missing a lot of data.

            insert into [TimeTracking-Test].[dbo].[clients] (clientid, active, clientname)
            select EntityID, IsActive, EntityName
            from inserted
            when TypeID in ('2','3')

    Thursday, August 07, 2014 8:43 PM
  • Fortunately the nature of the source database is such that it's impossible for there to be multiple additions in a single statement, which is why I'm not worried about that. Thanks for pointing it out just in case though.

    Huh? Anyone can run a multi-row INSERT statement from a query window. And that will happen sooner or later.

    Use the trigger that StefDBA posted. Not only is it safe - it is also simpler.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 07, 2014 9:55 PM
  • Huh? Anyone can run a multi-row INSERT statement from a query window. And that will happen sooner or later.

    Use the trigger that StefDBA posted. Not only is it safe - it is also simpler.

    Knowing our setup and who has access to run queries directly on the server I highly doubt that. Plus, since the app that adds records to that table also adds related information to other tables at the same time, if one of us did go nuts and decide to start adding companies manually then issues with this trigger would be the least of my problems.

    That said, I may well re-do the code using StefDBA's suggested method since it is simpler and removes an entire step from how I was trying to do it.

    Friday, August 08, 2014 6:09 AM
  • Hi,

    It's simpler and robust. Using scalar values is just a time bomb.

    From a query window:

    INSERT ...
    SELECT
    FROM (VALUES (...))

    Also, triggers copying data over tables are frequently a denormalization sign. In that case, you can get rid of the trigger, have less storage and possibly have better performances just by reviewing the database structure.

    Regards,

    Stef DBA


    • Edited by StefDBA Thursday, September 04, 2014 6:59 PM
    Thursday, September 04, 2014 6:49 PM