SQL trigger updating table using another table

Question

Hi everyone I am still sort of new to SQL, I have a slight problem and maybe someone can help.

I have researched all about triggers and what I read make sense, but I can't get the answer I need from it for some reason so I will explain what I need to do

I have 2 tables Products, LabelPrint

In products there are 5 columns upc, itemcode, description, price, labelprint

In LabelPrint there are the same columns

What I need is a trigger for when a new item is created or a old item is edited and the column LabelPrint is modified to =1 for yes

I need the item in question to be copied over to labelprint table

The label print table is automatically cleaned of this items after a certain period of time, I just hope someone can give me some help in understanding how I can make this trigger work the way I hope it will

thanks brandon

First of all: **what database system**, and which version, are you using?? *SQL* is just the *Structured Query Language* - a language used by many database systems, but not a a database product... Stuff like triggers is very vendor-specific - so we really need to know what **database system** you're using.... — marc_s, Aug 22 '12 at 16:38
ok as far a database structure i guess i would say im using sql 2008 r2 express structure? And Vikdor Products and LabelPrint are the table names — quatre432, Aug 22 '12 at 16:43

marc_s · Accepted Answer · 2012-08-23T03:37:03.047

I would try something like this:

CREATE TRIGGER InsertProducts
ON dbo.Products
FOR INSERT
AS 
   INSERT INTO dbo.LabelPrint(upc, itemcode, description, price, labelprint)
      SELECT
         upc, itemcode, description, price, labelprint
      FROM Inserted
      WHERE labelprint = 1

This first trigger will fire whenever you insert data into the table dbo.Products, and if the labelprint column is set to 1, then those rows are inserted into dbo.LabelPrint as well.

The UPDATE is a bit trickier:

CREATE TRIGGER UpdateProducts
ON dbo.Products
FOR UPDATE
AS 
   INSERT INTO dbo.LabelPrint(upc, itemcode, description, price, labelprint)
      SELECT
         i.upc, i.itemcode, i.description, i.price, i.labelprint
      FROM Inserted i
      INNER JOIN Deleted d ON i.itemcode = d.itemcode
      WHERE i.labelprint = 1 AND d.labelprint <> 1

Here, I check to see that the row has been updated (not 100% sure if itemcode is the best column to use to link the old and new values - adapt as needed if it's not), and I make sure the old value (from the Deleted pseudo-table) is not 1 while the new value (from Inserted) is 1 - in that case, the column LabelPrint has been updated to 1 and that row will be copied into the LabelPrint table.

One word of caution: you need to be aware that SQL Server does not fire the trigger for each row that is being inserted and/or updated. The trigger will fire once for each statement - and that one statement could very well update 50 rows - in that case, the trigger fires once, and the pseudo-tables inside the trigger (Inserted, Deleted) will contain 50 rows each. Just don't ever assume that Inserted only ever contains a single row - that assumption would be totally false.

marc_s the insert works, just tested it, i wrote a php program to insert data into the products table it only add's one product at a time i will test thoroughly, but i believe it will fire correctly, as far as the update i have a question about, it needs to update only when the value for labelprint columns is changed, itemcode will hardly be touched, what will happen is the customer will go into the product and edit it and check mark the label print button and save, what in the update code would you suggest changing? Also maybe this will help, after the customer prints there labels, — quatre432, Aug 22 '12 at 17:30
The customer prints there label the label table will be wiped clean and be blank for more input — quatre432, Aug 22 '12 at 17:33
@user1617578: try the UPDATE too - I think it should work as you require it to work. — marc_s, Aug 22 '12 at 20:37
update too? marc_s dont mean to sound dumb i scanned the page dont no what your talking about — quatre432, Aug 22 '12 at 20:56
Thanks marc i got the update working using a variation of your top code — quatre432, Aug 22 '12 at 21:16

SQL trigger updating table using another table

1 Answers1