我已经编写了代码来创建一个表并将 Products 表中的信息插入到 ProductsAudit 表中。我的问题是,当我将数据插入 Products 表时,我收到一个错误,告诉我 DateUpdated 列不允许 NULL 值。我正在尝试插入 GETDATE() 并且无法找出导致问题的原因。
USE MyGuitarshop
GO
CREATE TABLE ProductsAudit(
AuditID int PRIMARY KEY IDENTITY,
ProductID int NOT NULL,
CategoryID int NOT NULL,
ProductCode varchar(10) NOT NULL,
ProductName varchar(255)NOT NULL,
ListPrice money NOT NULL,
DiscountPercent money NOT NULL,
DateUpdated datetime2 NOT NULL);
GO
CREATE TRIGGER ProductsAudit_UPDATE
ON Products
AFTER INSERT, UPDATE
AS
INSERT INTO
ProductsAudit (ProductID, CategoryID, ProductCode, ProductName, ListPrice, DiscountPercent, DateUpdated)
SELECT
Products.ProductID,
Products.CategoryID,
Products.ProductCode,
Products.ProductName,
Products.ListPrice,
Products.DiscountPercent,
DateAdded AS DateUpdated
FROM Products
GO
INSERT INTO
Products (CategoryID, ProductCode, ProductName, Description, ListPrice,DiscountPercent, DateAdded)
Values
(1, '229715', 'Sushi', 'Great food for hungry students', 9.99, 12, GETDATE());
GO
USE MyGuitarShop
SELECT * FROM ProductsAudit
我想你可能想AuditID
成为一个identity
专栏:
CREATE TABLE ProductsAudit(
AuditID int IDENTITY PRIMARY KEY,
一旦你这样做了,你就不需要在插入中指定该列。你也可能意味着使用getdate()
了DateUpdated
,特别是因为你要插入空入DateAdded
列Product
。
INSERT INTO
ProductsAudit (ProductID, CategoryID, ProductCode, ProductName, ListPrice, DiscountPercent, DateUpdated)
SELECT
ProductID,
CategoryID,
ProductCode,
ProductName,
ListPrice,
DiscountPercent,
GetDate() AS DateUpdated
FROM INSERTED
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句