Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cannot Create - Data Insertion Trigger Expand / Collapse
Author
Message
Posted Friday, January 30, 2009 1:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 13, 2012 9:01 AM
Points: 11, Visits: 49
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:

[color=red]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'.[/color]

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?
Post #647282
Posted Sunday, February 01, 2009 11:38 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #647734
Posted Sunday, February 01, 2009 12:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 13, 2012 9:01 AM
Points: 11, Visits: 49
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!
Post #647739
Posted Sunday, February 01, 2009 12:17 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
No problem, it happens to all of us. Just glad that I could help. :)

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #647740
Posted Sunday, February 01, 2009 12:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 13, 2012 9:01 AM
Points: 11, Visits: 49
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
Post #647744
Posted Sunday, February 01, 2009 12:52 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
Good Luck.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #647745
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse