请教一个sql触发器的问题

问题描述：

现在的需求是，向一个表tb_Student_Work插入一条记录前，将字段state为1的全部改为1
请问该怎么实现

我的触发器语句是

CREATE TRIGGER tri_tb_Student_WorkByUpdate
ON tb_Student_Work
FOR INSERT
AS
BEGIN
    DECLARE @subject char(50);
    --把新增的值赋给变量
    SELECT @subject=_subject FROM inserted;
    UPDATE tb_Student_Work SET _state = -1 WHERE _subject=@subject AND _state = 1
END

但结果是新插入的那条记录的state值也成了-1，我想让它的值不变

答

CREATE TRIGGER tri_tb_Student_WorkByUpdate
ON tb_Student_Work
FOR INSERT
AS
BEGIN
DECLARE @subject char(50);
--把新增的值赋给变量
SELECT @subject=_subject FROM inserted; --取主键
UPDATE tb_Student_Work SET _state = -1 WHERE _subject<>@subject AND _state = 1
END

请教一个sql触发器的问题

相关推荐