找到原始记录并仅更改不插入的记录

Question

我已经尝试过，但到目前为止还没有成功。由于我对查询的了解有限，我想我最好将其发布在这里。

我有具有以下结构的学生表

create table students(
id int not null primary key identity,
sname varchar(25),
status varchar(25),
renew varchar(15),
enrollment datetime,
)

我有许多学生，他们有 ID，studentName(sname),status('active' 或 'not-active'), renew('no' 表示新学生，yes' 表示续订学生）和入学日期。

insert into students values('jay','active','no','2010-01-01')
insert into students values('Phil','active','no','2010-01-01')
insert into students values('Cru','active','no','2010-01-01')
insert into students values('slow','active','no','2010-01-01')
insert into students values('true','active','no','2010-01-01')
insert into students values('false','active','no','2010-01-01')

现在我有一个 INSERT 触发器，它应该在更新学生时停用旧学生。因此，如果我插入以下将续订设置为“是”的内容，它应该使现有记录变为“非活动”。

insert into students values('false','active','yes','2011-01-01')

我编写了这个 INSERT 触发器，它可以工作，但它会使旧的和新的插入记录都处于非活动状态。我只想停用原始记录。此外，原始记录和插入记录之间不仅注册日期和新字段不同，其余字段都相同。如何解决这个问题？这是我的触发器

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tr_renew_student]
ON [dbo].students
-- WITH ENCRYPTION
FOR INSERT
-- WITH APPEND
-- NOT FOR REPLICATION
AS
-- insert sql here
if exists(select * from inserted where Renew = 'yes')
BEGIN

UPDATE students 
    SET status = 'Inactive'

    FROM Inserted i
        INNER JOIN students T2
            ON i.sname = T2.sname

END

请注意，这非常接近我的问题。谢谢

Answer 1

将您的更新更改为：

UPDATE students 
    SET status = 'Inactive'

    FROM Inserted i
        INNER JOIN students T2
            ON i.sname = T2.sname
            AND i.id <> t2.id

这将检查您正在更新的行不是新插入的行。

Answer 2

你看过@@identity吗？几年前，我处理过类似的事情，并使用 @@identity 来获取最后创建的身份值；基本上获取最新的身份值，然后设置除通过 @@identity 返回的 ID 之外的所有符合条件的记录。

在这里阅读有关身份值获取者的信息：
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

已添加：您对插入表的看法是正确的。如果您不想/不能使用插入的表，您的触发器可能如下所示。

SELECT @@identity //  <- this gets the last identity value inserted.

UPDATE students
SET status = 'Inactive'
WHERE students.name = (SELECT name FROM students WHERE id = @@identity)
  AND id <> @@identity

注：凭记忆写的，未经测试。