January 30, 2009 at 1:03 pm
I am trying to work an exercise in Microsoft Press SQL Server 2005 Database Essentials Step-by-Step.
In chapter 10 there is a section where the following table is created:
CREATE TABLE [Production].[ProductHistory](
[ProductID] [int] NOT NULL,
[Name] [dbo].[Name] NOT NULL,
[ProductNumber] [nvarchar](25) NOT NULL,
[MakeFlag] [dbo].[Flag] NOT NULL CONSTRAINT [DF_Product_MakeFlagH] DEFAULT ((1)),
[FinishedGoodsFlag] [dbo].[Flag] NOT NULL CONSTRAINT [DF_Product_FinishedGoodsFlagH] DEFAULT ((1)),
[Color] [nvarchar](15) NULL,
[SafetyStockLevel] [smallint] NOT NULL,
[ReorderPoint] [smallint] NOT NULL,
[StandardCost] [money] NOT NULL,
[ListPrice] [money] NOT NULL,
[Size] [nvarchar](5) NULL,
[SizeUnitMeasureCode] [nchar](3) NULL,
[WeightUnitMeasureCode] [nchar](3) NULL,
[Weight] [decimal](8, 2) NULL,
[DaysToManufacture] [int] NOT NULL,
[ProductLine] [nchar](2) NULL,
[Class] [nchar](2) NULL,
[Style] [nchar](2) NULL,
[ProductSubcategoryID] [int] NULL,
[ProductModelID] [int] NULL,
[SellStartDate] [datetime] NOT NULL,
[SellEndDate] [datetime] NULL,
[DiscontinuedDate] [datetime] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Product_ModifiedDateH] DEFAULT (getdate()),
[Action] [nchar](1) NULL,
[UserName] [nvarchar](100) NULL CONSTRAINT [DF_Product_UserNameH] DEFAULT (user_name()),
CONSTRAINT [PK_ProductHistory] PRIMARY KEY CLUSTERED
(
[ProductID] ASC,
[ModifiedDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [Production].[ProductHistory] WITH CHECK ADD CONSTRAINT [CK_ProductHistory] CHECK (([Action] like '[I,M,D]'))
GO
ALTER TABLE [Production].[ProductHistory] CHECK CONSTRAINT [CK_ProductHistory]
Then the following Trigger is Created:
CREATE TRIGGER Production.Product_InsertTrigger
ON Production.Product
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [AdventureWorks].[Production].[ProductHistory]
([ProductID]
,[Name]
,[ProductNumber]
,[MakeFlag]
,[FinishedGoodsFlag]
,[Color]
,[SafetyStockLevel]
,[ReorderPoint]
,[StandardCost]
,[ListPrice]
,[Size]
,[SizeUnitMeasureCode]
,[WeightUnitMeasureCode]
,[Weight]
,[DaysToManufacture]
,[ProductLine]
,[Class]
,[Style]
,[ProductSubcategoryID]
,[ProductModelID]
,[SellStartDate]
,[SellEndDate]
,[DiscontinuedDate]
,[rowguid]
,[ModifiedDate]
,[Action]
,[UserName])
SELECT [ProductID]
,[Name]
,[ProductNumber]
,[MakeFlag]
,[FinishedGoodsFlag]
,[Color]
,[SafetyStockLevel]
,[ReorderPoint]
,[StandardCost]
,[ListPrice]
,[Size]
,[SizeUnitMeasureCode]
,[WeightUnitMeasureCode]
,[Weight]
,[DaysToManufacture]
,[ProductLine]
,[Class]
,[Style]
,[ProductSubcategoryID]
,[ProductModelID]
,[SellStartDate]
,[SellEndDate]
,[DiscontinuedDate]
,[rowguid]
,[ModifiedDate]
,[Action]
,[UserName]
FROM INSERTED
END
This trigger parses fine... When I execute it I get the following errors:
Msg 207, Level 16, State 1, Procedure Product_InsertTrigger, Line 60
Invalid column name 'Action'.
Msg 207, Level 16, State 1, Procedure Product_InsertTrigger, Line 61
Invalid column name 'UserName'.
If I change the CREATE TRIGGER and put FROM [AdventureWorks].[Production].[ProductHistory] in place of "INSERTED" the code executes without error.
I'm stumped. Can anyone tell me what to do here?:crying:
February 1, 2009 at 11:38 am
I do not see your listing for the Production.Product table. Since that is the table that your Trigger is on, then that is the format that the INSERTED table has in that trigger and NOT the format of the Production.ProductHistory table.
So I assume that the Production.Product table does not have the [Action] or [Username] columns.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 1, 2009 at 12:14 pm
Thanks for pointing out my sever idiocy... I was not paying attention to the code in the book... the SELECT portion of that trigger should have been
"
SELECT [ProductID]
,[Name]
,[ProductNumber]
,[MakeFlag]
,[FinishedGoodsFlag]
,[Color]
,[SafetyStockLevel]
,[ReorderPoint]
,[StandardCost]
,[ListPrice]
,[Size]
,[SizeUnitMeasureCode]
,[WeightUnitMeasureCode]
,[Weight]
,[DaysToManufacture]
,[ProductLine]
,[Class]
,[Style]
,[ProductSubcategoryID]
,[ProductModelID]
,[SellStartDate]
,[SellEndDate]
,[DiscontinuedDate]
,[rowguid]
,GetDate()
,'I'
,USER_NAME()
FROM INSERTED
"
thanks for point out to me that I was not paying attention.
Have a great day!
February 1, 2009 at 12:17 pm
No problem, it happens to all of us. Just glad that I could help. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 1, 2009 at 12:42 pm
part of the problem with this Microsoft Press book is that it has some mistakes in it... and it was made for Server Manager, not Server Manager Express...
So, in the interest of keeping my sanity, I ordered a coy of Developer version so I can use Server Manager.
I'm trying to cram this SQL down my into my brain to take 70-431 SQL test for MCTS, and the final test for my MCSA.
Thanks again for your help.:D
February 1, 2009 at 12:52 pm
Good Luck.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply