CODEWITHSUNDEEP
sql-server-2008triggers
user793468
user793468

Reputation: 4976

Understanding SQL CURSOR statement

Can someone please explain what the INSERTED means after FROM? there is no INSERTED Table in the trigger which has this below sql. Does the INSERTED refer to the table which has the trigger?

  DECLARE ChgCursor CURSOR FOR SELECT ACCID,USERID,GCID,........ FROM INSERTED
  SELECT  @ppid = ppid FROM sysdba.SYSTEMINFO WHERE SYSTEMINFOID = 'PR111'

Upvotes: 0

Views: 32

Answers (1)

marc_s
marc_s

Reputation: 755157

A SQL Server trigger has two pseudo tables:

  • Inserted which contains the newly inserted rows (in an INSERT trigger) or the updated (new) values in an UPDATE trigger

  • Deleted, which contains the rows that were deleted (in a DELETE trigger), or the old values (before the update) in an UPDATE trigger

Both tables are present only within triggers and will have the identical columns as the underlying table they're "connected" to (by means of the trigger being defined on a table).

SQL Server triggers will be fired once per statement (not once per row) and thus if any of the statements affects multiple rows, then Inserted and/or Deleted will contain multiple rows - you always need to keep this in mind and code your triggers accordingly.

See more details e.g. here Data Points: Exploring SQL Server triggers

And a word of warning: a trigger in SQL Server should always be very lean and mean - do NOT do extended and time consuming processing in a trigger, and this also means try to avoid cursors in a trigger at least twice as hard as you should in other T-SQL code.

Upvotes: 2

Related Questions