Cannot Create - Data Insertion Trigger

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Newbies » Cannot Create - Data Insertion Trigger

Cannot Create - Data Insertion Trigger

Rate Topic

Display Mode

Topic Options

Author

Message

aniccadhamma

Posted Friday, January 30, 2009 1:03 PM

Grasshopper

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? Crying

Post #647282

RBarryYoung

Posted Sunday, February 01, 2009 11:38 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

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

aniccadhamma

Posted Sunday, February 01, 2009 12:14 PM

Grasshopper

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

RBarryYoung

Posted Sunday, February 01, 2009 12:17 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

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

aniccadhamma

Posted Sunday, February 01, 2009 12:42 PM

Grasshopper

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

RBarryYoung

Posted Sunday, February 01, 2009 12:52 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

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 »

Permissions