Triggers : Name references "inserted"

• Digs

  SSCertifiable

  Points: 7333

  More actions

  March 17, 2005 at 10:43 am

  #164877

  Hi,

  I see that when reading examples the word 'Inserted' or 'deleted' is used to reference the recordset. Plus I see table reference "i" like 'i.fieldname', WHAT is this, cant find anything in this on BOL, maybe I am searching wrong, were can I find info on this ?

  example

  -- Create the trigger.

  CREATE TRIGGER tr_tTable_Alter_insert

  ON dbo.tTable_Alter

  FOR INSERT

  as

  IF (SELECT COUNT(*) FROM INSERTED

  WHERE intNameID > 100) > 0

  BEGIN

  PRINT 'Error: Your authority does not allow you to insert a manager into the table'

  ROLLBACK TRANSACTION

  END

  GO

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  March 17, 2005 at 11:06 am

  #547617

  In BOL, type "triggers" in the Index, and open the section titled "inserted tables":

   

  Using the inserted and deleted Tables

  Two special tables are used in trigger statements: the deleted table and the inserted table. Microsoft® SQL Server™ 2000 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for trigger actions; however, you cannot alter the data in the tables directly.

• Brian Laws

  SSCommitted

  Points: 1556

  More actions

  March 17, 2005 at 11:11 am

  #547619

  The "i" reference is a table alias. It's a usefull shorthand, particularly when dealing with long table names.

  For example, the following query:

  SELECT tblProductsWebSetting.SKU, tblProductsWebSetting.Title, tblProductsBibliographicInfo.Author FROM tblProductsWebSetting LEFT JOIN tblProductsBilbiographicInfo ON tblProductsWebSetting.SKU = tblProductsBilbiographicInfo.SKU

  can be much easier written as:

  SELECT ws.SKU, ws.Title, bi.Author FROM tblProductsWebSetting AS "ws" LEFT JOIN tblProductsBilbiographicInfo AS "bi" ON ws.SKU = bi.SKU

  (BTW, the AS and quotes are not necessary. I usually don't use them, i.e. FROM tblProductsWebSetting ws)

  It's very handy. I use it constantly.

  HTH!