Trigger Issue

• mtz676

  SSCrazy Eights

  Points: 9212

  More actions

  September 8, 2009 at 12:39 pm

  #141356

  MainTable ( ID , Tab1ID,Tab2ID)

  Table1(Tab1ID,Tab1Description)

  Table2(Tab2ID,Tab2Description)

  CREATE Trigger dbo.trigger122 on dbo.MainTable for INSERT

  as

  declare @body varchar(2000)

  declare @ID varchar(100)

  declare @rc int

  declare @Tab1description varchar(2000)

  declare @Tab2Description varchar(2000)

  declare @body varchar(2000)

  select @Id= I.ID , @Tab1Description= L.Tab1Description

  , @Tab2Description=LS.Tab2Description

  from Inserted I

  inner join Table1 L on I.Tab1ID = L.Id

  inner join Table2 LS on I.Tab2ID=LS.ID

  where I.Tab1ID = '12' or I.Tab1ID = '14'

  SET @rc = @@ROWCOUNT

  IF @rc > 0

  BEGIN

  SET @body = 'ID is '+ ' '+ @ID + ' ' + 'and Table1description is' + ' '+ @Tab1Description + ' '+ 'Table2Description is ' + ' '+ @Tab2Description

  insert into dbo.TriggerLog

  select @body

  EXEC master..xp_sendmail

  @recipients = 'xyz@yahoo.com',

  @subject = 'Inserted a new row',

  @message = @body

  END

  I am unable to retrieve table description in this code . If I remove table2 description from the code(remove + ' '+ 'Table2Description is ' + ' '+ @Tab2Description from the body ) then the trigger works fine .

  Appreciate any assiatance

  Thanks

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  September 8, 2009 at 12:54 pm

  #1050731

  We cannot see the error from here. Could you tell us what it is?

  [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]

• mtz676

  SSCrazy Eights

  Points: 9212

  More actions

  September 8, 2009 at 1:36 pm

  #1050761

  Error :

  [Microsoft][ODBC SQL Server Driver][SQL Server Mail Sent.

  [Microsoft][ODBC SQL Server Driver] String or Binary Data would be truncated.

  [Microsoft][ODBC SQL Server Driver]The statement has been terminated.

• OTF

  SSCertifiable

  Points: 6104

  More actions

  September 8, 2009 at 8:19 pm

  #1050881

  From what I can see, it would appear that you are attempting to stuff:

  @Tab1description varchar(2000)

  and

  @Tab2Description varchar(2000)

  into

  @body varchar(2000)

  Thats probably why you are getting the 'string would truncate' error and why it works when you omit one of the @tab*Description variables.