CREATE TRIGGER UpdatePreqBrand_trigger ON PO_PREQ_LIN
AFTER INSERT
AS
BEGIN
update PO_PREQ_LIN
set PO_PREQ_LIN.COMMNT_1 = IM_ITEM.ADDL_DESCR_2
from PO_PREQ_LIN, IM_ITEM 
where PO_PREQ_LIN.ITEM_NO = IM_ITEM.ITEM_NO and PREQ_NO IN (SELECT PREQ_NO FROM INSERTED);
END;

Row cannot be located for updating. Some values may have been changed since it was last read
  Row cannot be located for updating. Some values may have been changed since it was last read
  [Insert]
  [SELECT * FROM [PO_PREQ_LIN] WHERE (1=0) ]

quote:

---

Originally posted by aharvestofhealth

There are two primary keys in this table, and both are numbers. I'm not sure what an identity field is or how to find that out.

---

SELECT         
               c.TABLE_NAME
              ,c.COLUMN_NAME
              ,c.TABLE_CATALOG
              ,c.TABLE_SCHEMA 
FROM         
              INFORMATION_SCHEMA.COLUMNS c 
WHERE         COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME)
                      ,c.COLUMN_NAME,'IsIdentity') = 1 
AND           c.TABLE_NAME = 'yourtable'

quote:

---

Originally posted by aharvestofhealth

Ok, I replaced 'yourtable' with 'PO_PREQ_LIN' and the script ran successfully, however it returned no results.

---

quote:

---

Originally posted by Kristen

"And the error I get when I try to make any changes (e.g. deleting an item):"

This is an INSERT trigger, so won't fire on a DELETE (unless there is a Delete Trigger there which is trying to INSERT back again )

The error should indicate the line number but also the name of the object where the error occurred. Is that showing the name of the trigger (it will, helpfully, if that's where the error is occurring)

---

quote:

---

Originally posted by aharvestofhealth

@visakh16
No, the script doesn't update any of the primary keys.

@vijayisonly
Yes, after disabling the trigger, I can make changes to the table just fine.

---

quote:

---

Originally posted by aharvestofhealth

@visakh16
No, the script doesn't update any of the primary keys.

@vijayisonly
Yes, after disabling the trigger, I can make changes to the table just fine.

---

quote:

---

whats datatype of PO_PREQ_LIN.COMMNT_1 column?

SQL Server MVP

---

quote:

---

Why not get the value from IM_ITEM during insert itself? Why do you need a trigger to update it after the INSERT has been done?

---

quote:

---

Originally posted by aharvestofhealth

quote:

---

whats datatype of PO_PREQ_LIN.COMMNT_1 column?

SQL Server MVP

---

AlphaNumeric - basically the brand name of an item.

quote:

---

Why not get the value from IM_ITEM during insert itself? Why do you need a trigger to update it after the INSERT has been done?

---

The software that I use does not use the IM_ITEM table in the purchasing area. I need the field IM_ITEM.ADDL_DESCR_2 and the only way I found to do this is to copy it to the PO_PREQ_LIN.COMMNT_1. It works fine when I run the script by itself, but I want it to run every time I create a new purchase order.

This script works just fine alone just not with the trigger:

update PO_PREQ_LIN
set PO_PREQ_LIN.COMMNT_1 = IM_ITEM.ADDL_DESCR_2
from PO_PREQ_LIN, IM_ITEM
where PO_PREQ_LIN.ITEM_NO = IM_ITEM.ITEM_NO

---

quote:

---

Originally posted by Kristen

@vijayisonly: I'd be inclined to have that logic in a trigger, myself. Then nothing can get data into the data which isn't "valid" for the associated IM_ITEM column.

@aharvestofhealth: Although I would ask the questions:

What should happen if ADDL_DESCR_2 (in IM_ITEM) is subsequently changed? Does PO_PREQ_LIN.COMMNT_1 remain unchanged? (Given that the column is called "Comment" I reckon you are only trying to get a "This is the initial ADDL_DESCR_2" into it)

What about if there is no corresponding row in IM_ITEM when a row is inserted into PO_PREQ_LIN? and in that case what about when the first corresponding row is subsequently added in IM_ITEM?

What about if there are multiple corresponding rows in IM_ITEM when PO_PREQ_LIN is inserted? I reckon SQL will choose which one to use to update PO_PREQ_LIN.COMMNT_1 at "random"

Picky points only, I'm sure you've considered them.

---

quote:

---

Originally posted by visakh16
do you mean varchar when you say it is AlphaNumeric?

---

quote:

---

Originally posted by visakh16

were you trying to do data changes from application?
see below it seems like error with ado recordset

http://support.microsoft.com/kb/193515

SQL Server MVP

---