?
Solved

SQL Trigger - Invalid Column Name

Posted on 2013-01-24
4
Medium Priority
?
1,588 Views
Last Modified: 2013-01-26
Hi

I am trying to run the trigger below but keep getting the following error:

(Msg 207, Level 16, State 1, Procedure trg_UpdateCost, Line 22
Invalid column name 'xfPositionTitle'.
Msg 207, Level 16, State 1, Procedure trg_UpdateCost, Line 23
Invalid column name 'xfAvailabilityFacilityType'.)




CREATE trigger [dbo].[trg_UpdateCost] on [dbo].[Contact]
after insert
as
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for trigger here
   
    Declare @RECID varchar(32),
    @PositionTitle varchar(50),
    @FacilityType Varchar(25)
   
      SELECT @RECID = Inserted.RecID,
      @PositionTitle = xfPositionTitle,
      @FacilityType = xfAvailabilityFacilityType
      
      FROM INSERTED
      
      If
      xfPositionTitle = 'Physiotherapist'
      and xfAvailabilityFacilityType = 'Regis'
      
      BEGIN
            update Contact
            SET xfAvailabilityChargeOutRate = '75.00'
            from contact
            where recid = @RECID
      END


END


HELP???
0
Comment
Question by:ahmzb1990
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 39

Expert Comment

by:appari
ID: 38817198
try this
CREATE trigger [dbo].[trg_UpdateCost] on [dbo].[Contact]
after insert
as
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for trigger here
    
--    Declare @RECID varchar(32),
--    @PositionTitle varchar(50),
--    @FacilityType Varchar(25)
--    
--      SELECT @RECID = Inserted.RecID,
--      @PositionTitle = xfPositionTitle,
--      @FacilityType = xfAvailabilityFacilityType
--     
--      FROM INSERTED
--      
--      If @PositionTitle = 'Physiotherapist'
--			and @FacilityType = 'Regis'
--      BEGIN
--            update Contact
--            SET xfAvailabilityChargeOutRate = '75.00'
--            from contact
--            where recid = @RECID
--      END


	update C
		SET xfAvailabilityChargeOutRate = '75.00'
    from contact C join inserted ins
         on ins.recid = C.recid
	where ins.xfPositionTitle = 'Physiotherapist'
		and ins.xfAvailabilityFacilityType = 'Regis'

END

Open in new window


modified your existing code too and commented out that part. but your code handles only single row inserts.
0
 

Author Comment

by:ahmzb1990
ID: 38817303
Thanks mate that worked perfectly, only thing is i need to use the if statement.

Reason being is there will be a few conditions i.e.

If  ins.xfPositionTitle = 'Eye Care'
and xfAvailabilityFacilityType = 'Non Regis'

then SET xfAvailabilityChargeOutRate = '90.00'

please help??
0
 
LVL 39

Accepted Solution

by:
appari earned 2000 total points
ID: 38817346
for that just copy the update statement i added and change the conditions and value.
CREATE trigger [dbo].[trg_UpdateCost] on [dbo].[Contact]
after insert
as
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for trigger here
    
--    Declare @RECID varchar(32),
--    @PositionTitle varchar(50),
--    @FacilityType Varchar(25)
--    
--      SELECT @RECID = Inserted.RecID,
--      @PositionTitle = xfPositionTitle,
--      @FacilityType = xfAvailabilityFacilityType
--     
--      FROM INSERTED
--      
--      If @PositionTitle = 'Physiotherapist'
--			and @FacilityType = 'Regis'
--      BEGIN
--            update Contact
--            SET xfAvailabilityChargeOutRate = '75.00'
--            from contact
--            where recid = @RECID
--      END


	update C
		SET xfAvailabilityChargeOutRate = '75.00'
    from contact C join inserted ins
         on ins.recid = C.recid
	where ins.xfPositionTitle = 'Physiotherapist'
		and ins.xfAvailabilityFacilityType = 'Regis'

	update C
		SET xfAvailabilityChargeOutRate = '90.00'
    from contact C join inserted ins
         on ins.recid = C.recid
	where ins.xfPositionTitle = 'Eye Care'
		and ins.xfAvailabilityFacilityType = 'Non Regis'
END

Open in new window

0
 

Author Closing Comment

by:ahmzb1990
ID: 38823427
Worked like a charm
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question