CODEWITHSUNDEEP
sql-servert-sql
user3570765
user3570765

Reputation: 51

Adding data and renaming the column

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

Upvotes: 0

Views: 1355

Answers (2)

marc_s
marc_s

Reputation: 755321

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?

Upvotes: 1

dean
dean

Reputation: 10098

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)

Upvotes: 2

Related Questions