Adding data and renaming the column

Question

I keep getting a conversion error, but what I need to do is add data from one table, and change the DateAdded column to a DateUpdated column in the new table. The data from the original table is:

ProductID (PK, int, NOT NULL)
CategoryID (FK, int, NULL)
ProductCode (varchar(10), NOT NULL)
ProductName(varchar(255), NOT NULL)
Description(Text, NOT NULL)
ListPrice(money, NOT NULL)
DiscountPercent(money, NOT NULL)
DateAdded(datetime, NULL)

I created a new table:

CREATE TABLE ProductsAudit
(AuditID int NOT NULL PRIMARY KEY,
 ProductID int NOT NULL,
 CategoryID int NULL,
 ProductCode varchar(10) NOT NULL,
 ProductName varchar(255) NOT NULL,
 DateUpdated datetime NULL,
 ListPrice money NOT NULL,
 DiscountPercent money NOT NULL);

Now I need to add 1 column and change the name of one column, here is what I did:

USE MyGuitarShop;
GO

IF OBJECT_ID('Products_UPDATE') IS NOT NULL
DROP TRIGGER Products_UPDATE
GO

CREATE TRIGGER Products_UPDATE
ON Products
AFTER INSERT, UPDATE
AS
    INSERT ProductsAudit
    SELECT AuditID, Products.ProductID, Products.CategoryID, Products.ProductCode, 
    Products.ProductName,Products.ListPrice, Products.DiscountPercent, DateAdded AS DateUpdated
    FROM Products JOIN ProductsAudit
    ON ProductsAudit.AuditID = (SELECT AuditID FROM inserted)

I need to change the DateAdded column to the DateUpdated name, but insert all of the data from the original

First of all: why a trigger? If you need to copy the data from one table to another - just use a INSERT INTO ... SELECT ..... Also: your trigger has a fundamental flaw in that you expect it to be called for each row - this is NOT the case in SQL Server, and the Inserted pseudo table can (and will!) contain multiple rows - so which one are you selecting in your (SELECT ProductID FROM inserted) statement? It's undefined - plus you're ignoring all the other rows in Inserted, too! — marc_s
– marc_s, Commented May 3, 2014 at 21:23
yeah maybe i dont understand the inserted as well as I should. but I think whats confusing me is its giving me a conversion from datetime to money and I do not see where that would be there problem — user3570765
– user3570765, Commented May 3, 2014 at 21:28

dean · Accepted Answer · 2014-05-03 21:36:50Z

2

You didn't specify the column list for your insert statement, so the columns are being matched by their ordinal positions. It happens that ListPrice from Products is matched to DateUpdated from ProductsAudit. Do it like this:

INSERT ProductsAudit (AuditID, ProductID, CategoryID, ProductCode, 
  ProductName, ListPrice, DiscountPercent, DateUpdated)
SELECT AuditID, Products.ProductID, Products.CategoryID, Products.ProductCode, 
  Products.ProductName,Products.ListPrice, Products.DiscountPercent, DateAdded
FROM Products JOIN ProductsAudit
ON ProductsAudit.AuditID = (SELECT AuditID FROM inserted)

answered May 3, 2014 at 21:36

dean

10.1k2 gold badges27 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2014-05-04 08:28:13Z

As @dean said - you didn't specify the list of columns to insert data into - and you must have mixed up something along the way.

Building on his answer - my gripe is that you don't take into account that Inserted will contain multiple rows - and you need to fix that, too!

This is what your trigger should look like in the end:

CREATE TRIGGER Products_UPDATE
ON Products
AFTER INSERT, UPDATE
AS
    -- define the INSERT and include the list of columns to insert into!
    INSERT ProductsAudit (AuditID, ProductID, CategoryID, ProductCode, 
                          ProductName, ListPrice, DiscountPercent, DateUpdated)
       -- select data from the Inserted table - could be multiple rows! 
       -- join it to the ProductsAudit table via the ProductID (as PK)
       SELECT 
          pa.AuditID, 
          i.ProductID, i.CategoryID, i.ProductCode, 
          i.ProductName, i.ListPrice, i.DiscountPercent, i.DateAdded
      FROM Inserted i
      JOIN ProductsAudit pa ON pa.ProductID = i.ProductID

HOWEVER: this will only work if you already have an entry for any given ProductID in your ProductsAudit table - how are you handling cases when there's no entry in the ProductsAudit table yet? How are you inserting a new row and determining a new AuditID for that row?

Collectives™ on Stack Overflow

Adding data and renaming the column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related