Update column in table a base on condition in table b

Question

I am trying to write a trigger in Table1 to update a column in Table2 based on a condition in Table1. I am not that good in SQL but enough to be dangerous. This script is not recognizing the column col2 in Table1.

Create TRIGGER trgupd on Table1 
After insert
AS 
    If Inserted.col2 = '1'
    BEGIN
        Update Table2
        Set col3 = '2000' + the inserted value
        From Table2
        inner join inserted I on Table1.ID = Table2.ID
    End

Well, the biggest issue is: since the trigger might be called once for an entire **batch** of inserts (which might add 25 rows to your table), the check `If Inserted.col2 = '1'` is useless - the `Inserted` table will contain **25 rows** - so which of those are you checing against that `1` value??? It's non-deterministic. You need to **rewrite your triggers** to take into account that `Inserted` *can* (and **WILL!**) contain **multiple rows** having been inserted, when it fires. You **CANNOT** assume there's only a single row in `Inserted` — marc_s, Feb 11 '16 at 17:10
what error you get? Invalid Comlumn ?? BTW, you can take a look on http://stackoverflow.com/questions/2178889/sql-server-a-trigger-to-work-on-multiple-row-inserts to handle multi-row DLM — FLICKER, Feb 11 '16 at 17:32
The thing is only 1 row is inserted, the program I am using will not give me the option for more than 1. The reason I need to update this column because the value is too small and I need to print it as a barcode. If it's too small then the barcode reader will not read it. — Han Jr., Feb 11 '16 at 18:58

score 0 · Answer 1 · answered Feb 11 '16 at 17:49

Just make it an Update:

Create TRIGGER trgupd on Table1 
After insert
AS 
    BEGIN
    Update Table2
    Set col3 = '2000' + the inserted value
    From Table2
    inner join inserted I on I.ID = Table2.ID -- note I.ID not Table1.ID
    where I.col2 = 1;
END

The reason yours is failing is that inserted is a table or recordset and inserted.col2 hasn't actually got a row selected. Just add a where clause to your update.

Also, note the join for inserted: Table1 is wrong (it's not in that query); it needs to be I.

Thank you Simon, it installed with no errors however it's not updating the field. If I ran Set col3 = '2000' + the inserted value by itself in a query it will update the field Not sure what I am missing — Han Jr., Feb 11 '16 at 18:49

Update column in table a base on condition in table b

1 Answers1