CODEWITHSUNDEEP
sqlsql-servert-sql
moe
moe

Reputation: 5249

issue with trigger function in sql

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

Upvotes: 1

Views: 93

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

@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;

Upvotes: 0

nvogel
nvogel

Reputation: 25526

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.

Upvotes: 8

Related Questions