ahmzb1990
asked on
SQL Trigger - Invalid Column Name
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 'xfAvailabilityFacilityTyp e'.)
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 xfAvailabilityChargeOutRat e = '75.00'
from contact
where recid = @RECID
END
END
HELP???
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 'xfAvailabilityFacilityTyp
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
BEGIN
update Contact
SET xfAvailabilityChargeOutRat
from contact
where recid = @RECID
END
END
HELP???
ASKER
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 xfAvailabilityChargeOutRat e = '90.00'
please help??
Reason being is there will be a few conditions i.e.
If ins.xfPositionTitle = 'Eye Care'
and xfAvailabilityFacilityType
then SET xfAvailabilityChargeOutRat
please help??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Worked like a charm
Open in new window
modified your existing code too and commented out that part. but your code handles only single row inserts.