CODEWITHSUNDEEP
sqlsql-server
Ahmad
Ahmad

Reputation: 12737

Unable to create an [After UPDATE] trigger

I am trying to create a trigger that runs every time an update takes place.

Basically: I have two tables: plans that contains the master/header rows, and plan_courses that contain the details. plans has and id primary key, and plan_courses has a plan_id as foreign key.

Once a course is attended, I am supposed to count all courses within a plan and check if the attended count equal that total number of courses in a given plan. If so, then the whole header/master row should be marked as done. Otherwise, it shall be marked as not done.

Here is what I tried:

create trigger my_trigger on plan_courses 
after update 
as
begin

  declare @attended_cnt int;
  declare @total_cnt int;
  declare @plan_status varchar;

  select @total_cnt = count(*) from plan_courses where plan_id=inserted.plan_id;

  select @attended_cnt=count(*) from plan_courses 
         where plan_id=inserted.plan_id and course_status='attended';

  select @plan_status = case when @attended_cnt < @total_cnt then 'not done' else 'done' end;


  update plans set plan_status = @plan_status where id=inserted.plan_id;


end;

When I execute this, I get:

SQL Server Database Error: The multi-part identifier "inserted.plan_id" could not be bound.

What am I doing wrong?

Upvotes: 0

Views: 44

Answers (3)

Paul Karam
Paul Karam

Reputation: 4210

Here you can find a few tips on what's wrong with your trigger.

INSERTED is a reference to the UPDATED row, but in form of a table (If you want to call it like that).

What you should do to fix your queries is joining your table with the INSERTED in order to achieve your results: 

create trigger my_trigger on plan_courses 
after update 
as
begin

  declare @attended_cnt int;
  declare @total_cnt int;
  declare @plan_status varchar;

  select @total_cnt = count(*) 
  from plan_courses as p
  inner join inserted as j
    on p.plan_id=j.plan_id;

  select @attended_cnt=count(*) 
  from plan_courses as p
  inner join inserted as j
    on p.plan_id = j.plan_id and p.course_status = 'attended';

  select @plan_status = case when @attended_cnt < @total_cnt then 'not done' else 'done' end;


  update plans 
  set plan_status = @plan_status 
  from plans as p
  inner join inserted as i
    on i.plan_id = p.id;


end;

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28900

you should join the tables and use inserted tables and not like below

plan_id=inserted.plan_id

you should do like below

 select @attended_cnt=count(*) from plan_courses p
                      join
                      inserted i on p.plan_id=i.plan_id and course_status='attended';

Upvotes: 0

IVNSTN
IVNSTN

Reputation: 9299

INSERTED is a temp table here.

select @total_cnt = count(*) from INSERTED

msdn

Upvotes: 0

Related Questions