Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cannot Create - Data Insertion Trigger


Cannot Create - Data Insertion Trigger

Author
Message
aniccadhamma
aniccadhamma
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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
RBarryYoung
RBarryYoung
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10334 Visits: 9517
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."
aniccadhamma
aniccadhamma
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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!
RBarryYoung
RBarryYoung
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10334 Visits: 9517
No problem, it happens to all of us. Just glad that I could help. Smile

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
aniccadhamma
aniccadhamma
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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.BigGrin
RBarryYoung
RBarryYoung
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10334 Visits: 9517
Good Luck.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search