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

Answer 1

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.