Link to home
Start Free TrialLog in
Avatar of mahmood66
mahmood66Flag for United Arab Emirates

asked on

how can i find the line# in trigger.

i am using SQL Server 2005
i have written below code.
i am entering 10 line and while pressing the save button my this trigger fires. and its give me my error message which is mention in my RaisError statement. i want to know which line is a cause of raising the error.

ALTER TRIGGER [fExchangeRateCheck]
   ON  [dbo].[_etblARAPBatchLines]
  AFTER Insert
AS
BEGIN
Declare @vCurrencyID as INT
Declare @vCurrencyRate as FLOAT

      If exists (select * from inserted where fExchangeRate <> 3.67 and fExchangeRate  <>0.98 and fExchangeRate  <>1 and fExchangeRate  <> 13.70  )
            Begin
                  RaisError('ExchangeRate is not Valid',16,1)
            End
      Else
            Begin
                  select @vCurrencyID=iAccountCurrencyID, @vCurrencyRate=fExchangeRate from Inserted
                              If (@vCurrencyID= 0 and @vCurrencyRate<>1) or (@vCurrencyID= 1 and @vCurrencyRate<>3.67) or (@vCurrencyID= 2 and @vCurrencyRate<>0.98) or (@vCurrencyID= 3 and @vCurrencyRate<>13.70)
                                    Begin
                                          RaisError('Account Currency and Exchange Rate are Mismatch',16,1)
                                    End
            End
End
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

you mean, which "line of data" ?
for that, you need to fetch the ID field of the row, and append to the error message
ALTER TRIGGER [fExchangeRateCheck]
   ON  [dbo].[_etblARAPBatchLines]
  AFTER Insert
AS
BEGIN
Declare @vCurrencyID as INT
Declare @vCurrencyRate as FLOAT


      select @vCurrencyID=iAccountCurrencyID
           , @vCurrencyRate=fExchangeRate 
        from Inserted

      If (@vCurrencyRate <> 3.67 and @vCurrencyRate <>0.98 and @vCurrencyRate<>1 and @vCurrencyRate<> 13.70  )
            Begin
                  RaisError('ExchangeRate is not Valid:' + @vCurrencyID,16,1)
            End
      Else
            Begin
                              If (@vCurrencyID= 0 and @vCurrencyRate<>1) or (@vCurrencyID= 1 and @vCurrencyRate<>3.67) or (@vCurrencyID= 2 and @vCurrencyRate<>0.98) or (@vCurrencyID= 3 and @vCurrencyRate<>13.70) 
                                    Begin
                                          RaisError('Account Currency and Exchange Rate are Mismatch',16,1)
                                    End
            End
End

Open in new window

Avatar of mahmood66

ASKER

i am not having ID field of the row. i am having  one idBatchlines. eg. below are the data of idbatchlines.

idBatchLines       col           col1           col3
1879895              data1      data2        data3
1879896              data1      data2        data3
1879898              data1      data2        data3

if 2nd row is having error. system must display that
RaisError "Account Currency and Exchange Rate are Mismatch in row no  2"
i hope you understand.
it is possible ...

 
ALTER TRIGGER [fExchangeRateCheck]
   ON  [dbo].[_etblARAPBatchLines]
  AFTER Insert
AS
BEGIN
Declare @vCurrencyID as INT
Declare @vCurrencyRate as FLOAT

declare @rn int

      If exists (select * from inserted where fExchangeRate <> 3.67 and fExchangeRate  <>0.98 and fExchangeRate  <>1 and fExchangeRate  <> 13.70  )
            Begin
           select @rn = rn
              from (select row_number() over (order by vCurrencyID) rn
                      from inserted
                    ) sq
                where fExchangeRate <> 3.67 
                  and fExchangeRate  <>0.98
                  and fExchangeRate  <>1 
                  and fExchangeRate  <> 13.70
          
                  RaisError('ExchangeRate is not Valid for row:' + cast(@rn as varchar(10)),16,1)

            End
      Else
            Begin
                  select @vCurrencyID=iAccountCurrencyID, @vCurrencyRate=fExchangeRate from Inserted
                              If (@vCurrencyID= 0 and @vCurrencyRate<>1) or (@vCurrencyID= 1 and @vCurrencyRate<>3.67) or (@vCurrencyID= 2 and @vCurrencyRate<>0.98) or (@vCurrencyID= 3 and @vCurrencyRate<>13.70)
                                    Begin
                                          RaisError('Account Currency and Exchange Rate are Mismatch',16,1)
                                    End
            End
End 

Open in new window


I would suggest that you put the possible exchange rate values into a table ...
so it is more flexible
Dear, i am still not clear about your given code. i have implemented your code, but it gives error.
i want to tell you that my below block mostly will execute.  i want to get rowno which is raising the error.

 Begin
                  select @vCurrencyID=iAccountCurrencyID, @vCurrencyRate=fExchangeRate from Inserted
                              If (@vCurrencyID= 0 and @vCurrencyRate<>1) or (@vCurrencyID= 1 and @vCurrencyRate<>3.67) or (@vCurrencyID= 2 and @vCurrencyRate<>0.98) or (@vCurrencyID= 3 and @vCurrencyRate<>13.70)
                                    Begin
                                          RaisError('Account Currency and Exchange Rate are Mismatch',16,1)
                                    End
            End
what errors do you get?

note that with your version, you will only check if the first line is mismatching on the exchange rates!
i am getting below error.


Msg 207, Level 16, State 1, Procedure fExchangeRateCheck, Line 48
Invalid column name 'fExchangeRate'.
Msg 207, Level 16, State 1, Procedure fExchangeRateCheck, Line 49
Invalid column name 'fExchangeRate'.
Msg 207, Level 16, State 1, Procedure fExchangeRateCheck, Line 50
Invalid column name 'fExchangeRate'.
Msg 207, Level 16, State 1, Procedure fExchangeRateCheck, Line 51
Invalid column name 'fExchangeRate'.
>fExchangeRate
sorry, but that is exactly the name of what you had written ?!
this is the same field name in "_etblARAPBatchLines" table. i am also surprise why i am getting this error even Field name is correct.
sorry, I see it now
ALTER TRIGGER [fExchangeRateCheck]
   ON  [dbo].[_etblARAPBatchLines]
  AFTER Insert
AS
BEGIN
Declare @vCurrencyID as INT
Declare @vCurrencyRate as FLOAT

declare @rn int

      If exists (select * from inserted where fExchangeRate <> 3.67 and fExchangeRate  <>0.98 and fExchangeRate  <>1 and fExchangeRate  <> 13.70  )
            Begin
           select @rn = rn
              from (select fExchangeRate 
                         , row_number() over (order by vCurrencyID) rn
                      from inserted
                    ) sq
                where fExchangeRate <> 3.67 
                  and fExchangeRate  <>0.98
                  and fExchangeRate  <>1 
                  and fExchangeRate  <> 13.70
          
                  RaisError('ExchangeRate is not Valid for row:' + cast(@rn as varchar(10)),16,1)

            End
      Else
            Begin
                  select @vCurrencyID=iAccountCurrencyID, @vCurrencyRate=fExchangeRate from Inserted
                              If (@vCurrencyID= 0 and @vCurrencyRate<>1) or (@vCurrencyID= 1 and @vCurrencyRate<>3.67) or (@vCurrencyID= 2 and @vCurrencyRate<>0.98) or (@vCurrencyID= 3 and @vCurrencyRate<>13.70)
                                    Begin
                                          RaisError('Account Currency and Exchange Rate are Mismatch',16,1)
                                    End
            End
End

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
partially solved. i did not get full solution.