Results 1 to 4 of 4

Thread: Trigger issue

  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Unanswered: Trigger issue

    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


    The error it shows when I try inserting data into the database directly is :
    [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.


    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
    Last edited by metro17; 09-08-09 at 20:04.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Before we talk about your specific problem, please let's talk about this trigger.

    This does not work if you ever do set based INSERTs. Only one record will have it's data sent. Triggers should always support set based operations because that is how SQL processes the best and how expert database programmers code.

    Number 2, you should never ever connect to an outside application in the body of a transaction. Turn off your SMTP server and try to insert into your table.

    Number 3, how does this work fine if @body is declared twice.

    As for your error, I suggest you check the data type of the description field in the table.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Sep 2009
    Posts
    3
    I am trying insert only 1 record.
    Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    and are you the only who will ever use this table? even if you are, will you remember that in 2 years?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •