June 2, 2009 at 2:05 pm
This seems to me like a pretty basic question, but I'm having trouble finding an example.
I have a table that has a boolean field (field B) to be set based on the value of another field (field A) in the table. Whenever field A changes I want to check its value and then set field B if the value of field A falls within a given range. I figured a trigger was the way to do this but I'm having difficulty coding the trigger.
Here's what I have:
[Code]CREATE TRIGGER trigger_PMS
ON SP_Phases
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
IF UPDATE (dtOrder) --did write date change
BEGIN--yes, write date changed, is it now current for PMS?
IF dtOrder BETWEEN dbo.udf_FirstOfMonth(0) AND dbo.udf_EndOfMonth(1)
BEGIN
UPDATE SP_Phases
SET bPMS = 1--yes it is, set PMS flag
FROM INSERTED
END
END
END
GO[/Code]
and here's the error SQL 2k5 gives me when I try to execute the code to create the Trigger:
[Quote]Msg 207, Level 16, State 1, Procedure trigger_PMS, Line 21
Invalid column name 'dtOrder'.
Msg 207, Level 16, State 1, Procedure trigger_PMS, Line 21
Invalid column name 'dtOrder'.[/Quote]
dtOrder is a valid Column in the SP_Phases table that is being updated
~mj
June 2, 2009 at 11:30 pm
You can't reference a column's value outside of a query, your "IF dtOrder.." statement has no rowset to reference the column to. Try this instead:
CREATE TRIGGER trigger_PMS
ON SP_Phases
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
IF UPDATE (dtOrder) --did write date change
BEGIN --yes, write date changed, is it now current for PMS?
UPDATE SP_Phases
SET bPMS = 1 --yes it is, set PMS flag
FROM INSERTED
WHERE dtOrder BETWEEN dbo.udf_FirstOfMonth(0) AND dbo.udf_EndOfMonth(1)
END
END
GO
[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]
June 3, 2009 at 2:30 pm
Thanks! I did figure out that I was referencing incorrectly but I didn't think of doing it that way, which is easier than what I ended up doing. I ended up declaring and setting some variables to do my testing before updating again.
Thanks again!
~mj
June 4, 2009 at 2:32 am
Glad 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]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply