Problem Declaring variables inside a Trigger

• gaurav-404321

  SSCommitted

  Points: 1593

  More actions

  August 27, 2009 at 12:34 am

  #135077

  Hi,

  I am creating a after insert trigger. I am declaring some variables and doing some calculation.

  Attached is the code for regference. I dont know where i am makign a mistake while executing the trigger it is giving following error:

  Msg 207, Level 16, State 1, Procedure tGenerateSalary, Line 33

  Invalid column name '@MonthDays'.

  Msg 207, Level 16, State 1, Procedure tGenerateSalary, Line 33

  Invalid column name '@LWOP'.

  Msg 207, Level 16, State 1, Procedure tGenerateSalary, Line 33

  Invalid column name '@Absent'.

  Msg 207, Level 16, State 1, Procedure tGenerateSalary, Line 33

  Invalid column name '@CasualLeave'.

  Msg 207, Level 16, State 1, Procedure tGenerateSalary, Line 33

  Invalid column name '@CompensatoryLeave'.

  Msg 207, Level 16, State 1, Procedure tGenerateSalary, Line 33

  Invalid column name '@DutyLeave'.

  Msg 207, Level 16, State 1, Procedure tGenerateSalary, Line 33

  Invalid column name '@VacationLeave'.

• Michael Grassman-401202

  Valued Member

  Points: 74

  More actions

  August 27, 2009 at 1:23 am

  #1045358

  The issue is with the last line of code.

  SET @WorkingDays=(([@MonthDays]-([@LWOP]+[@Absent]*(2)))-((([@CasualLeave]+[@CompensatoryLeave])+[@DutyLeave])+[@VacationLeave]))

  Remove the brackets [ ] around the variable names.

  SET @WorkingDays=((@MonthDays-(@LWOP+@Absent*(2)))-(((@CasualLeave+@CompensatoryLeave)+@DutyLeave)+@VacationLeave))

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  August 27, 2009 at 2:09 am

  #1045370

  Hi

  You can handle your complete work within one statement:

  DECLARE @WorkingDays decimal(18,0),

  @LWOP decimal(5,2),

  @Absent decimal(5,2),

  @CasualLeave decimal(5,2),

  @CompensatoryLeave decimal (5,2),

  @DutyLeave decimal (5,2),

  @VacationLeave decimal(5,2),

  @MonthDays int

  UPDATE EmployeeSalarySheetNew SET

  @LWOP = ISNULL(LWOP,0)

  ,@Absent = ISNULL(Absent, 0)

  ,@CasualLeave = ISNULL(CasualLeave, 0)

  ,@CompensatoryLeave = ISNULL(CompensatoryLeave, 0)

  ,@DutyLeave = ISNULL(DutyLeave, 0)

  ,@VacationLeave = ISNULL(VacationLeave, 0)

  ,@MonthDays = ISNULL(MonthDays, 0)

  ,BasicAfteMerger= BasicRate

  SET =(SELECT MonthDays FROM EmployeeSalarySheetNew)

  --Calulate WorkingDays

  SET @WorkingDays=((@MonthDays-(@LWOP+@Absent*(2)))-(((@CasualLeave+@CompensatoryLeave)+@DutyLeave)+@VacationLeave))

  BTW: Your last line has no effect since you don't do any further tasks with @WorkingDays

  Greets

  Flo