issue with trigger function in sql

Question

I am trying to create a trigger function after items are inserted into a table but i am getting a syntax error and not sure what is going on. Can someone please help? Here is my syntax:

GO
CREATE TRIGGER trgAfterInsert ON  [DR].[dbo].[Derived_Values]
FOR INSERT
AS  
    declare @BusinessUnit varchar(75);
    declare @Questions varchar(max);
    declare @Answer nchar(10);


    select @BusinessUnit=i.@BusinessUnit from inserted i;   
select @Questions=i.@Questions from inserted i; 
select @Answer=i.@Answer from inserted i;   


    insert into [Main].[dbo].[Derived_Values_Test]
           (BusinessUnit,Questions, Answer) 
    values(@BusinessUnit,@Questions, @Answer);

    PRINT 'AFTER INSERT trigger fired.'
GO

here is error: Msg 102, Level 15, State 1, Procedure trgAfterInsert, Line 9 Incorrect syntax near '@BusinessUnit'. — moe
– moe, Commented May 30, 2013 at 20:50
select @BusinessUnit=i.@BusinessUnit from inserted i; Is your column really called @BusinessUnit? — Joachim Isaksson
– Joachim Isaksson, Commented May 30, 2013 at 20:51
@moe Then it should probably be select @BusinessUnit=i.BusinessUnit from inserted i;, however @sqlvogel's answer below has a better way of doing the entire thing. — Joachim Isaksson
– Joachim Isaksson, Commented May 30, 2013 at 20:56

nvogel · Accepted Answer · 2013-05-30 20:53:55Z

8

Try this:

CREATE TRIGGER trgAfterInsert ON  [DR].[dbo].[Derived_Values]
FOR INSERT
AS  
    insert into [Main].[dbo].[Derived_Values_Test]
           (BusinessUnit,Questions, Answer) 
    SELECT BusinessUnit,Questions, Answer
    FROM inserted;

    PRINT 'AFTER INSERT trigger fired.'

Never write triggers like yours which effectively assumes there will only be one row updated. Triggers should use set based logic.

answered May 30, 2013 at 20:53

nvogel

25.6k3 gold badges47 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

moe Over a year ago

thnx, i have tried your method and i did not get any error when i run it but it did not create the trigger in that database. Do i need to declare variables first or this is all what i need. not sure why it did not create the trigger

nvogel Over a year ago

You don't need any variables at all. Trigger gets created in the database where you run the CREATE TRIGGER statement.

moe Over a year ago

thanks. I am inserting the data a table that is remote server so when i run it i get this error ""The partner transaction manager has disabled its support for remote/network transactions."." Do you know what i need to do in order to resolve this? thanks

nvogel Over a year ago

If the target is a linked server then I suggest you don't use triggers because that will require a distributed transaction. Use a proc outside a transaction. Or use some other process to replicate the data (Service Broker, SSIS, or even just a remote view on the target server).

Gordon Linoff · Accepted Answer · 2013-05-30 20:56:43Z

0

@Answer is declared as a string, not a table. So this will not work:

select @Answer=i.@Questions from @Answer i;

Should this be inserted?

select @Answer=i.@Questions from inserted i;

Also, you appear to be using variables for column names:

select @BusinessUnit=i.BusinessUnit from inserted i;   
select @Questions=i.Questions from inserted i; 
select @Answer=i.Answerfrom inserted i;

edited May 30, 2013 at 20:56

answered May 30, 2013 at 20:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

moe Over a year ago

guys i am sorry i don't know how that happened but i updated now all my select statement but still getting the same error. see my initial post. thanks

Collectives™ on Stack Overflow

issue with trigger function in sql

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related