Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Operand type clash on trigger

    Hi folks,

    I've created a trigger and am getting a type clash with a uniqueidentifier and smalldatetime. Comparing the trigger with others i've done it looks ok.

    Msg 206, Level 16, State 2, Procedure new_request, Line 23
    Operand type clash: uniqueidentifier is incompatible with smalldatetime
    Code:
    CREATE TRIGGER new_request
       ON  dbo.Request 
       AFTER INSERT
    AS 
    BEGIN
        DECLARE @request_id uniqueidentifier, @requestor varchar(150), @short_description varchar(255), @category varchar(150), @priority varchar(20), @msg nvarchar(755), @due_date smalldatetime
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
    
        -- Insert statements for trigger here
        SET @request_id = (SELECT request_id FROM inserted i WHERE i.r_dept_id = 3 AND i.r_status = 'Open')
        SET @requestor = (SELECT r_reported_by FROM inserted i WHERE i.request_id = @request_id)
        SET @short_description = (SELECT r_short_description FROM inserted i WHERE i.request_id = @request_id)
        SET @category = (SELECT cat.category FROM dbo.Category cat INNER JOIN inserted i ON cat.cat_id = i.r_category WHERE i.request_id = @request_id)
        SET @priority = (SELECT p.priority FROM dbo.Priority p INNER JOIN inserted i ON p.priority_id = i.r_priority WHERE i.request_id = @request_id)
        SET @due_date = (SELECT r_date_required FROM inserted i WHERE i.request_id = @request_id)
        SET @msg = 'A new Celcat request has been made.'+ CHAR(13) + CHAR(10) +
                   'Here is a brief detail of the request:'+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
                   'Requestor: ' + @requestor + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
                   'Short Description: ' + @short_description + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
                   'Category: ' + @category + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
                   'Priority: ' + @priority + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
                   'Date Required: ' + @due_date + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
                   'Link: ' + 'http://rhc-apps2/ServiceLog/requests/Request.aspx?id=' + @request_id
    
        EXEC msdb.dbo.sp_send_dbmail
             @recipients = 'someone@somewhere.com',
             @subject = 'A new Celcat request has been made',
             @body = @msg;
    END
    When searching I keep finding it could be something to do with the fields being allowed null values, if it is this how can I get around it?
    <- Hides behind a rock.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You need to explicitly CAST your variables when concatenating them in to a string.

    Also, you might need to hide behind that rock....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Don't put this in a trigger.

    Instead create a scheduled job that polls your table for new records and sends out emails.
    George
    Home | Blog

  4. #4
    Join Date
    Sep 2005
    Posts
    240
    Quote Originally Posted by pootle flump
    You need to explicitly CAST your variables when concatenating them in to a string.

    Also, you might need to hide behind that rock....
    I think you may be right.
    I am of course referring to CAST the variables, not the hiding behind a rock

    I think I may need to change the uniqueidentifier to a varchar, as it seems to stop the ASP application from inserting into the table for some reason.

    Quote Originally Posted by gvee
    Don't put this in a trigger.

    Instead create a scheduled job that polls your table for new records and sends out emails.
    What would be the best way of checking for new records. In a trigger I can pick it up from the inserted table. Can I still use the inserted table, or would i need to create a boolean field in the table to check if I have sent an email or not?
    A scheduled job would probably fix the issue menioned above.
    <- Hides behind a rock.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Dump all records from INSERTED into a temp table once, and then leave INSERTED alone.

    Instead of choosing one or the other, leave your trigger intact, but replace a call to sp_send_dbmail with a call to sp_start_job. Create a manual job that will accept as parameters entries in some table, which can contain the message.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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