trigger issues - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

trigger issues

Author

Topic

awdeoseth
Starting Member

13 Posts

Posted - 2005-08-23 : 13:12:58

I am running a trigger on a table (cusomfields) that is to fire when the table is updated or inserted. The trigger takes the new data and moves it to another table called search_products. Search products is made up of 5 columns (product_id, search_terms, name, vendor and keywords). The search_terms column is a combo of the name, vendor and keywords columns.

When the trigger fires, the column in the customfields table that updates the keywords table in the search_terms field works. But the problem I am having is that the search_terms column is not being populated with that keyword value. Here is the trigger below, you can see what I am talking about.

Does anyone know why only part of the query in the trigger works?

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

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 = products.name + " " + vendors.name + " " + customfields.fieldvalue
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

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

Thanks

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-23 : 13:48:38

How big is the search_terms column? Could data be truncated?

Another thing you might try is to put ISNULL around all the values you are updating with. I am wondering if any of those three values are null, it might be setting the column to NULL.

Aj

Hey, it compiles.

awdeoseth
Starting Member

13 Posts

Posted - 2005-08-23 : 13:57:38

its varchar - 255, so I dont believe the data is being truncated.

The product name and vendor contain data before I set the keywords data. So there should be no problem with NULL values. When I insert the keywords from the customfields table, the data for name and vendor is already in the search_terms column. I just want to append the keywords data to the search_terms column data as well.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-23 : 16:58:14

I must be missing something because the only other thing I can think of is perhaps one of your joins is causing the problem.

Try making the joins LEFT joins and put the ISNULL function around each of those inserted values. Something like ISNULL(products.name, 'ProductName_Failed'), so you know which ones might be causing the problem.

Aj

Hey, it compiles.

awdeoseth
Starting Member

13 Posts

Posted - 2005-08-23 : 17:06:45

ok i changed the joins to left outer joins and added is isnull check. although I should tell you that the values of product name and vendor name will never be null cause they have to have values before you can assign customfields to the table.

my sql looks ok but the bloody code wont execute. well at least the 2nd part of the script wont.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-23 : 23:16:56

Post what you have so far and let's see if we can really screw it up!

Aj

Hey, it compiles.

awdeoseth
Starting Member

13 Posts

Posted - 2005-08-24 : 11:41:14

This is my new version, but still the same problem:

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

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

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

What do you think?

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-24 : 17:32:34

This is a total shot in the dark, but try putting brackets around the column 'name'.

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'

Since 'name' is a keyword, it might be causing the problem.

Hey, it compiles.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-08-25 : 05:32:22

What are data types of products.name and vendors.name column?

Maybe they are char(something)? If so use cast(table.name as varchar(255)) instead of table.name.

Search_products.keywords is updated, right?

awdeoseth
Starting Member

13 Posts

Posted - 2005-08-25 : 18:31:24

both columns are varchar already. The keywords column is updated in the search_products table. but the search_terms column is not.

ajthepoolman - I tried putting brackets around the column names but that did not help. The same results followed.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-26 : 09:38:46

I think that the last resort is to have you post the scripts to create the tables with some data so each of us can try and run this locally on our machines and try to figure out why it is failing.

Aj

Hey, it compiles.

awdeoseth
Starting Member

13 Posts

Posted - 2005-08-26 : 12:59:04

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','201')

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

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.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-26 : 15:18:52

OK, here is what I came up with. Hopefully I am not missing something that MUST be in place.

Change your WHERE clause to:

where customfields.fieldname = 'KEYWORDS' --search_products.product_id = products.product_id
--AND customfields.fieldname = 'KEYWORDS'

I found that Search_Products.product_id equals 1. But Products.Product_ID equals 120. Now, if this is a typo and Search_Products.product_id should of been 120, then let me know.

When I changed the WHERE clause, I am able to get all the values into the search_terms field. So, either your product_id was inserted incorrectly, or you should drop the constraint on that field.

Let me know if this works for you.

Aj

Hey, it compiles.

awdeoseth
Starting Member

13 Posts

Posted - 2005-08-26 : 16:35:02

ya, i'm sorry that product_id should be 120 not 1, it is a type, so am I just changing the where statement to get this to work?

awdeoseth
Starting Member

13 Posts

Posted - 2005-08-26 : 17:22:36

ok so i tried that but what happened was it changed every row in the keywords column to "test" (the data i used to test the trigger). This is a test machine so it isnt catestrophic that this happened.

But what is real interesting is that it updated every search_term row except the one i was trying to update. I have tons and tons of data from an online store, I didnt send that in the repro script cause I didnt want to bog you down with data you didnt need. But I need the constraint on the where to limit the update to just the product_id that I am updated. Otherwise all of the columns get updated.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-27 : 01:13:58

It sounds like you need to put the where clause back the way you had it. I figured it was a typo, but couldn't be certain.

The problem you are describing might be impossible. Since the Search_Terms table doesn't have any unique identifiers, it is always going to update every instance of Product_ID it comes across. Is that an accurate statement? Is it true that a unique Product_ID can be searched against multiple times?

Quick question, an entry in CustomFields is what fires the trigger that updates Search_Products right? Other than the trigger, is anything else inserting data into Search_Products? What I am getting at is, why are you running an UPDATE routine on this table? Wouldn't it make more sense to run an INSERT into Search_Products? That way you are inserting a new record each time a product is searched against, instead of updating all of the previous search instances.

Make sense? Again, I might be missing something more.

Aj

Hey, it compiles.

awdeoseth
Starting Member

13 Posts

Posted - 2005-08-29 : 12:41:02

Basically this is what I am trying to do with the trigger. Every time a product is added to the products table I want that product to be added to the search_products table. The product name, vendor name and any keywords (customfields.fieldvalue) that it may have, need to be added to the search_products table. In the name, vendor and keywords columns of the search_products table. Also each of those values need to be put into the search_terms.

I setup a trigger on the products table to send the name and vendor name of the product anytime one is added or updated. Because the keywords are store in a seperate table (customfields) that is only referenced through the product id (products.product_id --> customfields.foreignkey_id). I needed to put another trigger on the customfields table that would send that data to the search_products table whenever it was inserted or updated (I may be wrong putting a trigger on this table, correct me if I am).

So the issue I ran into was that the product name and vendor name is inserted and updated into the search_products keywords, name and vendor columns easily. The issue is the keywords. When the customfields are inserted, it is sent to the keywords column of search_products but not to the search_term column. So that in the search_terms column it would have the product name, vendor name and keywords.

Thats where I am stuck now. If I can eliminate some triggers or do it in more efficient way, then I am all ears.

Thank you for help.

awdeoseth
Starting Member

13 Posts

Posted - 2005-08-31 : 12:10:09

anyone out there?

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-31 : 13:33:05

Without having unique identifiers on your tables, I don't see how you could know which product needs updated.

Aj

Hey, it compiles.

awdeoseth
Starting Member

13 Posts

Posted - 2005-08-31 : 18:40:13

The trigger fires on the product table when a new row is added or either the product name or vendor_id columns are updated in the products table.