Sql server 2008 trigger not working after update query

Question

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

(1) Explain what the code should do. (2) Provide sample data and desired results. — Gordon Linoff, Sep 23 '19 at 11:18
"will this work for multiple rows"; No, if you have multiple rows, what will `@id` be set to? — HoneyBadger, Sep 23 '19 at 11:19
I have told the purpose of code in the first line. Also, I don't have any sample data and I don't think that is required. — Ayush Aggarwal, Sep 23 '19 at 11:20
@HoneyBadger sorry that was wrong code. I have updated my code according to other answer and it is still not working. — Ayush Aggarwal, Sep 23 '19 at 11:24

score 1 · Accepted Answer · answered Sep 23 '19 at 11:23

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.

Thanks, it worked. I will accept the answer in 6 mins as SO is not allowing me to do it now. — Ayush Aggarwal, Sep 23 '19 at 11:26

Sql server 2008 trigger not working after update query

1 Answers1