Sql Server 2000 Update Trigger on MS Access table

• Dustin Stumpf

  SSC Journeyman

  Points: 77

  More actions

  January 25, 2007 at 9:53 am

  #194023

  I have linked SQL Server 2000 tables in an MS Access DB so that our Marketing team may manipulate the data (yet allow us to backup the tables). I want to run an update trigger on one of the SQL tables linked into Access. Somehow however, the update does not work through Access yet works great when an update is performed through SQL Server.

  Here is the code I am using:

  CREATE TRIGGER UpdateTrigger ON dbo.[FOLIO MASTER]

  FOR Update

  AS

  Declare @Message varchar(3000);

  Set @Message = 'Updated';

  if (Update([Description]))

   Select @Message= @Message + '  Description to:' + Cast(Inserted.Description AS Varchar(10)) from Inserted;

  if (Update(Page))

   Select @Message= @Message + '  Page to:' + Cast(Inserted.Page AS Varchar(10)) from Inserted;

  if (Update([Space]))

   Select @Message= @Message + '  Space to:' + Cast(Inserted.[Space] AS Varchar(10)) from Inserted;

  if (Update(PUSource))

   Select @Message= @Message + '  PUSource to:' + Cast(Inserted.PUSource AS Varchar(10)) from Inserted;

  if (Update(CI))

   Select @Message= @Message + '  CI to:' + Cast(Inserted.CI AS Varchar(10)) from Inserted;

  if (Update(New))

   Select @Message= @Message + '  New to:' + Cast(Inserted.New AS Varchar(10)) from Inserted;

  if (Update(FolioNotes))

   Select @Message= @Message + '  FolioNotes to:' + Cast(Inserted.FolioNotes AS Varchar(10)) from Inserted;

  if (Update(RedPrice))

   Select @Message= @Message + '  RedPrice to:' + Cast(Inserted.RedPrice AS Varchar(10)) from Inserted;

  if (Update(MultiPricing))

   Select @Message= @Message + '  MultiPricing to:' + Cast(Inserted.MultiPricing AS Varchar(10)) from Inserted;

  -- add modifications to AuditLog table

  Insert dbo.AuditLog (ActionDescription, Sku, [Description], Page, [Space], BookName, PUSource, CI, New, FolioNotes,

   RedPrice, MultiPricing)

  Select @Message, Inserted.Sku, Inserted.[Description], Inserted.Page, Inserted.[Space], Inserted.BookName,

   Inserted.PUSource, Inserted.CI, Inserted.New, Inserted.FolioNotes, Inserted.RedPrice, Inserted.MultiPricing

  FROM Inserted, Dates

  WHERE GetDate() between [1stFolioDate] and FinalFolioDate and Inserted.BookName = Dates.BookName

  The @Message variable, which explains what field was updated, shows up 'Null' when an update is performed through Access. The @Message variable works perfect in SQL Server.

  Any ideas??

• Russel Loski

  SSCertifiable

  Points: 7059

  More actions

  January 25, 2007 at 12:17 pm

  #685666

  I'm going to take a stab.  Try:

  @Message + '  FieldName to:' + isnull(Cast(Inserted.FieldName AS Varchar(10)), ' NULL') 

  I think that Access is updating all of the columns, even if you only change a few of them.

  Russel Loski, MCSE Business Intelligence, Data Platform

• Dustin Stumpf

  SSC Journeyman

  Points: 77

  More actions

  January 25, 2007 at 1:28 pm

  #685683

  I'll give that a shot. Thanks for the help. I've also been told it may have to do with how the update queries are working in Access so I'll take a look at both options. Thanks for the help!