无法创建[After UPDATE]触发器

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.

基本上:我有两个表:包含主/标题行的计划,以及包含详细信息的plan_courses。 plans has和id主键,plan_courses有一个plan_id作为外键。

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.

SQL Server数据库错误:无法绑定多部分标识符“inserted.plan_id”。

What am I doing wrong?

我究竟做错了什么?

3 个解决方案

#1

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).

INSERTED是对UPDATED行的引用,但是以表格的形式(如果你想这样调用它)。

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

您应该如何修复查询,将表与INSERTED一起加入表中以获得结果:

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;

#2

INSERTED is a temp table here.

INSERTED是一个临时表。

select @total_cnt = count(*) from INSERTED

从INSERTED中选择@total_cnt = count(*)

msdn

#3

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

#1