Writing triggers for tables with text, ntext or image

• IceCreamWizard

  SSC Enthusiast

  Points: 171

  More actions

  November 16, 2007 at 5:27 am

  #751784

  Disclaimer: I'm new to SQL Server this year and I have not tried this solution. However, it may provide you with some ideas or prompt a more experience SQL Server developer with a better answer.

  I'm assuming the following...

  CREATE TABLE TableA (

  id int IDENTITY(1, 1) NOT NULL,

  docTitle nvarchar(200) NOT NULL,

  docText ntext NULL,

  docSize bigint NULL,

  PRIMARY KEY ([id])

  )

  Try the following...

  CREATE TRIGGER myProjTR_AU_TableA on TableA

  AFTER TRIGGER

  AS

  insert into

  TableA_Audit (Title, DocText, DocSize, DateUpdated)

  select

  i.docTitle, t.docText, i.DocSize, getDate()

  from

  inserted as i,

  TableA as t

  where

  i.id = t.id

  In short, since you're writing [font="Courier New"]AFTER UPDATE[/font] trigger, simply grab the info you need from the table itself vice the [font="Courier New"]inserted[/font] row.

• Jeff Moden

  SSC Guru

  Points: 1004664

  More actions

  November 16, 2007 at 7:01 am

  #751821

  So I have the following questions:

  1) Does SQL Server 2000 allow me to access ntext fields from the inserted or deleted tables? If so, why I am getting the above error?

  2) The error message suggests to me that the ntext fields are present in the table but I just can not use them. Am I right in thinking that? If so, is there any way I can access them?

  3) Does anyone know of any work around I can use to access these fields?

  1) No... just like BOL says, you cannot access Text, NText, or Image columns from the Inserted or Deleted tables.

  2) No, the "fields" are not present in the trigger. Only place holders for them are.

  3) Yes, and the IceCreamWizard's suggestion (above) is spot-on. Use the PK column(s) in the Inserted/Deleted tables to identify which rows you want to work in the "real" table that has the Text, NText, or Image columns.

  Please, don't tell me that you're trying to "audit" such columns...

  Use the IceCreamWizard's suggestion above. Use

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)