如何引用刚刚插入的值（在 TRIGGER 语句中）？

Question

CREATE TRIGGER ATTENDANCE_INSERTION_TRIGGER
ON course_enrollment
AFTER INSERT
AS
BEGIN

--insert **what just was inserted in course_enrollment** into course_schedule_attendance

END
GO

如何引用刚刚在 course_enrollment 中插入的内容？

Answer 1

试试这个：

CREATE TRIGGER ATTENDANCE_INSERTION_TRIGGER 
ON course_enrollment 
AFTER INSERT 
AS 
BEGIN

  --insert **what just was inserted in course_enrollment** into
  -- course_schedule_attendance  
  INSERT course_schedule_attendance  
      (course_id, student_id)
    SELECT
      course_id, student_id --you could use: INSERTED.course, INSERTED.student_id
      FROM INSERTED
END 
GO 

您也可以在单个 INSERT 中执行此操作，无需触发器：

--insert a single row in both tables at one time
INSERT course_enrollment 
      (course_id, student_id)
      OUTPUT course_id, student_id INTO course_schedule_attendance  
   VALUES (@xyz, @abc)

--or insert a set of rows into both at one time
INSERT course_enrollment 
      (course_id, student_id)
      OUTPUT course_id, student_id INTO course_schedule_attendance  
   SELECT
       xyz, abc
       FROM ...

Answer 2

您可以从插入的表中获取值

SELECT * FROM INSERTED I

因此，例如，如果 course_enrollment 有一个名为 course_id 和 Student_id 的字段，要获取这些字段，您可以执行以下操作

SELECT I.course_id,I.student_id FROM INSERTED I -- I is just an alias and is not needed but it helps

Answer 3

有两个有用的触发器元表，它们都与触发器所在的表的架构相匹配：INSERTED 和 DELETED。

有趣的是，如果您运行更新语句，触发器元表将在 INSERTED 和 DELETED 中都有行，DELETED 行对应于“更新之前”，而 INSERTED 对应于“更新后”。

这也使其对于审计目的很有用。