trigger issues

trigger issues

• awani

  Here is my first attempt at a repro script.  You will need 4 tables (products, search_terms, vendors and customfields).

  ===================================================

  1. Table Creations:

  CREATE TABLE [dbo].[PRODUCTS] (
   [Product_ID] [int] NOT NULL ,
   [Store_ID] [int] NULL ,
   [Category_ID] [int] NULL ,
   [Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
   [UnitPrice] [decimal](18, 4) NOT NULL ,
   [UnitRetail] [decimal](18, 4) NULL ,
   [Length] [decimal](18, 4) NULL ,
   [Width] [decimal](18, 4) NULL ,
   [Height] [decimal](18, 4) NULL ,
   [Manufacturer] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [SKU] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [DisplayPage] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [Theme] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [ReserveStock] [int] NULL ,
   [InvMode] [smallint] NULL ,
   [Image1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [Image1Params] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [Image2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [Image2Params] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [Description1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [Description2] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [Description3] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [Vendor_ID] [int] NULL ,
   [WrapGroup_ID] [int] NULL
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  GO

  CREATE TABLE [dbo].[CUSTOMFIELDS] (
   [CustomField_ID] [int] NOT NULL ,
   [Store_ID] [int] NOT NULL ,
   [TableName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
   [ForeignKey_ID] [int] NOT NULL ,
   [FieldName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [FieldValue] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
  ) ON [PRIMARY]
  GO

  CREATE TABLE [dbo].[Search_Products] (
   [Product_ID] [int] NULL ,
   [search_terms] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [vendor] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [keywords] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
  ) ON [PRIMARY]
  GO

  CREATE TABLE [dbo].[VENDORS] (
   [Vendor_ID] [int] NOT NULL ,
   [Store_ID] [int] NULL ,
   [AdminGroup_ID] [int] NULL ,
   [Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [Email] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [AutoNotify] [smallint] NULL
  ) ON [PRIMARY]
  GO

  ==========================================================

  2. Sample Data

  Products:

  insert into Products (product_id, name, unitprice, vendor_id)

  values ('120','product1','0.00','1')

  ---------------------------

  Vendors

  insert into Vendors (vendor_id, name)

  values ('201','vendor1')

  ---------------------------

  Search_Terms

  insert into Search_Terms (product_id, search_terms, name, vendor)

  values ('1','product1 vendor1','product1','vendor1')

  (Nothing needed to be added to customfields, the trigger will do that).

  ========================================

  3. Trigger that is causing the problem (on customfields table)

  CREATE TRIGGER tr_customfields_iu_1 ON Customfields
  AFTER INSERT, UPDATE
  AS

   update Search_Products
   set Search_Products.keywords = customfields.fieldvalue, Search_Products.search_terms = ISNULL(products.name,'ProductName_Failed') + ISNULL(vendors.name,'Vendor_Failed') + ISNULL(customfields.fieldvalue,'Customfields_Failed')
   from inserted
   inner join customfields on customfields.foreignkey_id = inserted.foreignkey_id
   inner join products on products.product_id = inserted.foreignkey_id
   inner join vendors on vendors.vendor_id = products.vendor_id
   where search_products.product_id = products.product_id AND customfields.fieldname = 'KEYWORDS'

  if @@error <> 0
    begin
     raiserror ('Keyword insert error: rolling back changes', 16, 1)
     rollback transaction
     return
    end
  return
  

  ==========================================

  4. Finally the insert from the customfields table that fires the trigger.

  insert into Customfields (customfield_id, store_id, tablename, foreignkey_id, fieldname, fieldvalue)

  values ('1','1','PRODUCTS','120','KEYWORDS','anothernameforproduct')

   

  Conclusion: I think that is all you need to reproduce what I am getting.  The customfield table is attached to the products table by way of the foreignkey_id in the customfields table. 

  The trigger is to take the fieldvalue and place it in the search_products - keywords column and then also put that fieldvalue in the search_terms column. But is hasnt so far.

  If you have any questions, please contact me.

   

  
  
  

• alan lan

  I am new to the SQL world, what do I need to do to post a sample schema

  
  
  

• rjherbein

  awdeoseth wrote:

  Well when the trigger runs on the customfields table, the name and vendor columns will not be null. They will always have data in them. So there are no issues with NULL values.

  
  
  Maybe worth trying before you post your repro script:
  You're concatenating 3 columns
  
  products.name + " " + vendors.name + " " + customfields.fieldvalue
  
  What about the customfields.fieldvalue column By any chance, can this be NULL
  Also, are these double quotation marks you're using Does it help when you do
  
  products.name + ' ' + vendors.name + ' ' + customfields.fieldvalue
  --
  Frank Kalis
  Microsoft SQL Server MVP
  http://www.insidesql.de
  Ich unterstutze PASS Deutschland e.V. (http://www.sqlpass.de)
  
  
  
  
  
  

• Roman S. Golubin

  Hi Seth - I've not had a chance to try the repro, but I have a couple suggestions. You mention that the keyword column is getting updated, but the search_terms column is not. Your update statement should affect both columns, not just one, so if it appears its not being updated, it could be that it truly is being updated, but to the same value. Also, are there any other triggers updating the search_product table
  
  When things get funky with triggers I always put some temporary diagnostics to help me see what is going on. Try putting your update logic into a SELECT statement that would return the values that would be applied to the columns in the update, similar to:

  select 'id being updated'=search_products.product_id,
  'new keyword value'=customfields.fieldvalue,
  'new search terms value'=products.name + " " + vendors.name + " " + customfields.fieldvalue
  <same from/where logic as your update>

  Also, you could put an update trigger on the search_product table, and return the data in the inserted/deleted tables to see the old/new values, and this would demonstrate my 'updating to the same value' theory.
  Good Luck! - Dean

  
  
  

• Elizabeth Maher MS

  Hey, for the first repro script that was really good. :-)
  In addition to what UC already said, may I add, that I would look into your design, if possible Do you really always need this VARCHAR(255) columns or can you narrow them down
  Also, theoretical hardliners might think your PRODUCTS table violates First Normal form as it contains repeated groups:
  
  [Image1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [Image1Params] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [Image2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [Image2Params] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [Description1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [Description2] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [Description3] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  
  
  So, as long as we don't talk about OLAP stuff here, in the long run you might be better off normalizing your structure.
  --
  Frank Kalis
  Microsoft SQL Server MVP
  http://www.insidesql.de
  Ich unterstutze PASS Deutschland e.V. (http://www.sqlpass.de)
  
  
  

• mhweiss

  Basically, you can convert the UPDATE to SELECT and see if it produces expected results. This will tell if the joins are correct for example. Btw, when I corrected the data in your sample script the trigger ran fine without any problem. So the code works it is your data that is probably inconsistent.
  
  

• Allison

  products.name + " " + vendors.name + " " + customfields.fieldvalue
  
  Can one of the column values above be NULL If so, then the resulting value will be NULL by default. You may have to use coalesce to check for null and return empty string for example.
  
  

• Aaron Winters

  You need to create a minimal repro that demonstrates the issue. That would encompass the following:
  
  1. CREATE TABLE statements
  2. INSERT statements (sample data)
  3. CREATE TRIGGER statement in your case
  4. Code or statements that reproes the problem. This can be a INSERT statement for example
  
  

• LabibaSeif

  Then I do not see any issues with the code as such. You need to post a sample schema with data and the trigger code that demonstrates the problem. It will be easier then to find the issue.
  
  

• zoulasc

  Hey everyone,

  First off thanks for your help. I do appreciate it!

  Lets down to business. The data that I entered into the repro script was just data I thought of in my head. If I messed up when I was creating it, you can change it so that it works. I.E. so that the vendor ID of the vendor table, matches the vendor_id of the products table. I guess I just made an error, my fault. 

  The table structure that you see if from the online shopping cart program Able Commerce. So I cannot change the way they created their tables and actually I excluded some of the table columns from the products tabe because they were "not nulls" and not needed for the trigger to work. I guess I should've mentioned that earlier.

  For Dean:  I do have another trigger on the PRODUCTS table that updates the product and vendor column in the search_terms table. That also sends those two columns into the search_terms column. But that trigger only fires when product name or vendor_id changes on the products table. 

  The (not working) customfields trigger only fires when the fieldvalue column is edited. But nonetheless I will try your SELECT suggestion and let you know what happened.

  Thanks again for your help.

  
  
  

• ScorpDaddy

  Frank: I tried your suggestions but nothing changed.  I even removed the products.name, etc from the script and replaced it with "dummy", just to see if I could get text into that search_terms column and that did not work.

  Any suggestions why that wouldnt work

  
  
  

• R.S

  Well when the trigger runs on the customfields table, the name and vendor columns will not be null. They will always have data in them. So there are no issues with NULL values.
  
  

• Neil K.

  The data is incorrect in your scripts. There are several problems:
  
  1. Vendor_Id in Products does not match that of Vendor.Vendor_ID
  2. CustomField.foreignkey_id value does not match any of the products
  
  So your trigger will not get any rows to update. If you correct the data it should be fine. You should also consider creating foreign key constraints on the tables.
  
  

• Madcowrus

  Dean - I am a little confused by your SELECT example, what am I selecting from again and is taking the place of my current trigger
  
  
• trigger issues