CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ayush Aggarwal
Ayush Aggarwal

Reputation: 177

Sql server 2008 trigger not working after update query

I have to implement a trigger on a table such that some columns get automatically updated each time a row is updated. I am using the following trigger syntax:

create trigger degreedSkillPlan_updated on degreedSkillPlan
after update 
as
if (select trigger_nestlevel() )> 1     return;

    declare @id varchar;

     update degreedSkillPlan set updatedDate = getdate(), lastModifiedDate = getDate() FROM inserted 
WHERE inserted.skillPlanId = skillPlanId; ;

but this is not working. I haven't really implemented any trigger before. I have read some tutorials online but I am unable to figure out what is wrong with this. Also, will this work for multiple rows as stated in the accepted answer of >Handling multiple rows in SQL Server trigger

Upvotes: 0

Views: 68

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Your code is wrong on multiple levels:

  • varchar with no length is bad and usually does the wrong thing.
  • Assuming insert has only one row is incorrect.
  • The body should be in a begin/end block.

So:

create trigger degreedSkillPlan_updated on degreedSkillPlan
after update  as
begin
    if (trigger_nestlevel() ) > 1 return;

    update degreedSkillPlan
        set updatedDate = getdate(),
            lastModifiedDate = getDate()
        where skillPlanId in (select i.skillPlanId from inserted i);
end;

Maybe this will fix your problem.

Upvotes: 1

Related Questions