Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005
    Posts
    1

    Unanswered: Syntax error converting the varchar value '*' to a column of data type int.

    Hi,
    This is my complete trigger.


    ALTER TRIGGER DeleteTriggerC_Middleware_Exception ON dbo.C_Middleware_Exception AFTER DELETE AS
    BEGIN
    DECLARE @RowCount AS VARCHAR
    SET @RowCount = @@ROWCOUNT

    DECLARE @TableName_Deleted AS VARCHAR(50)
    DECLARE @ErrorMsg_Deleted AS VARCHAR(255)
    DECLARE @AddlErrMsg_Deleted AS VARCHAR(255)
    DECLARE @SubjectArea_Deleted AS VARCHAR(25)
    DECLARE @CHKPT_Deleted AS VARCHAR(10)

    SELECT @TableName_Deleted = table_name , @ErrorMsg_Deleted = cast(error_msg AS varchar(255)) , @AddlErrMsg_Deleted = cast(addl_error_msg AS varchar(255)),
    @SubjectArea_Deleted = cast(SUBJECT_AREA AS varchar(25)), @CHKPT_Deleted = cast(CHKPT AS varchar(255))FROM DELETED
    --where subject_area = 'inventory'

    UPDATE dbo.Error_Log SET No_Of_Occurance = No_Of_Occurance + @RowCount WHERE Table_Name = @TableName_Deleted
    IF @@ROWCOUNT = 0
    BEGIN
    UPDATE dbo.Error_Log SET No_Of_Occurance = No_Of_Occurance + @RowCount WHERE @ErrorMsg_Deleted like Error_Msg and Subject_Area IS NULL and ChkPoint IS NULL
    END
    IF @@ROWCOUNT = 0
    BEGIN
    UPDATE dbo.Error_Log SET No_Of_Occurance = No_Of_Occurance + @RowCount WHERE @AddlErrMsg_Deleted like Addl_Error_Msg
    END
    IF @@ROWCOUNT = 0
    BEGIN
    UPDATE dbo.Error_Log SET No_Of_Occurance = No_Of_Occurance + @RowCount WHERE @ErrorMsg_Deleted like Error_Msg and Subject_Area = @SubjectArea_Deleted and ChkPoint = @CHKPT_Deleted
    END

    END



    when i am executing the follwing query i am gettin systax error.

    Query :delete from dbo.c_middleware_exception where subject_area = 'eap_room'

    Error :
    Server: Msg 245, Level 16, State 1, Procedure DeleteTriggerC_Middleware_Exception, Line 17
    Syntax error converting the varchar value '*' to a column of data type int.



    what could be the solution.

    Thanks in advance
    yvnsmca
    Last edited by yvnsmca; 10-14-05 at 04:12.

  2. #2
    Join Date
    May 2002
    Posts
    299
    what is the datatype for dbo.Error_Log(No_Of_Occurance). '*' + @RowCount (int) will force an implicit conversion to INT which results in such error.
    --
    -oj
    http://www.rac4sql.net

Posting Permissions

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