Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    5

    Unhappy Unanswered: Image data within Update Trigger

    I have an INSTEAD OF UPDATE, INSERT Trigger.

    I pull the key fields from the inserted record,
    I then establish whether or not the record exists already,
    if it does exist I want to use Update to write the data within the 'Inserted' record to the table,
    If it does not exist I want to use Insert to write the data within the 'Inserted' record to the table.


    My problem comes when I try to use a column of type image,

    I can't create a variable of type image, so I can't figure out how to get the image data into the table from 'Inserted'???

    Can anyone help, please.

    thanks,

    Conan

    Here is my FLAWED Trigger code :-
    (only tries to handle update so far)

    CREATE TRIGGER UpdateAndCreateFileID ON DST_QUEST_RESPONSE
    INSTEAD OF INSERT, UPDATE
    AS

    DECLARE @iSurvey_NBR int,
    @sUser_ID varchar(255),
    @iQuestion_ID bigint,
    @iAnswer_CD int,
    @iScore_AMT int,
    @sResponse_Txt varchar(4000),
    @iFile_ID bigint,
    @sFile_Name varchar(255),
    @sFile_Type_CD varchar(255),
    @sFile_Size_Qty varchar(50),
    @Document_Data image

    --Get the fields needed from the inserted record.
    SELECT @iSurvey_NBR = DS4_SURVEY_NBR,
    @sUser_ID = DS4_USER_ID,
    @iQuestion_ID = DS4_QUESTION_ID,
    @iAnswer_CD = DS4_ANSWER_CD,
    @iScore_AMT = DS4_SCORE_AMT,
    @sResponse_Txt = DS4_RESPONSE_TXT,
    @iFile_ID = DS4_FILE_ID,
    @sFile_Name = DS4_FILE_NM,
    @sFile_Type_CD = DS4_FILE_TYPE_CD,
    @sFile_Size_Qty = DS4_FILE_SIZE_QTY,
    @Document_Data = DS4_DOCUMENT_DATA
    FROM Inserted

    --Check to see if there is already an entry in the response table, so to update only- the key fields
    IF EXISTS (SELECT 'X' FROM DST_QUEST_RESPONSE
    WHERE DS4_SURVEY_NBR = @iSurvey_NBR AND DS4_USER_ID = @sUser_ID AND DS4_QUESTION_ID = @iQuestion_ID)
    BEGIN
    --Row already exists, so update
    UPDATE DST_QUEST_RESPONSE
    SET DS4_ANSWER_CD = @iAnswer_CD,
    DS4_SCORE_AMT = @iScore_AMT,
    DS4_RESPONSE_TXT = @sResponse_Txt,
    DS4_FILE_ID = @iFile_ID,
    DS4_FILE_NM = @sFile_Name,
    DS4_FILE_TYPE_CD = @sFile_Type_CD,
    DS4_FILE_SIZE_QTY = @sFile_Size_Qty,
    DS4_DOCUMENT_DATA = @Document_Data

    WHERE DS4_SURVEY_NBR = @iSurvey_NBR AND DS4_USER_ID = @sUser_ID AND DS4_QUESTION_ID = @iQuestion_ID
    END

  2. #2
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: Image data within Update Trigger

    You cannot use an image type var in a trigger but you can refer an image text field from inserted table though.
    Try to fetch the value directly from inserted table for that column, something like:

    UPDATE DST_QUEST_RESPONSE
    SET DS4_ANSWER_CD = I.DS4_ANSWER_CD,
    :
    :
    :
    DS4_DOCUMENT_DATA = I. DS4_DOCUMENT_DATA
    FROM DST_QUEST_RESPONSE , inserted I
    WHERE ....



    Originally posted by cbraniff
    I have an INSTEAD OF UPDATE, INSERT Trigger.

    I pull the key fields from the inserted record,
    I then establish whether or not the record exists already,
    if it does exist I want to use Update to write the data within the 'Inserted' record to the table,
    If it does not exist I want to use Insert to write the data within the 'Inserted' record to the table.


    My problem comes when I try to use a column of type image,

    I can't create a variable of type image, so I can't figure out how to get the image data into the table from 'Inserted'???

    Can anyone help, please.

    thanks,

    Conan

    Here is my FLAWED Trigger code :-
    (only tries to handle update so far)

    CREATE TRIGGER UpdateAndCreateFileID ON DST_QUEST_RESPONSE
    INSTEAD OF INSERT, UPDATE
    AS

    DECLARE @iSurvey_NBR int,
    @sUser_ID varchar(255),
    @iQuestion_ID bigint,
    @iAnswer_CD int,
    @iScore_AMT int,
    @sResponse_Txt varchar(4000),
    @iFile_ID bigint,
    @sFile_Name varchar(255),
    @sFile_Type_CD varchar(255),
    @sFile_Size_Qty varchar(50),
    @Document_Data image

    --Get the fields needed from the inserted record.
    SELECT @iSurvey_NBR = DS4_SURVEY_NBR,
    @sUser_ID = DS4_USER_ID,
    @iQuestion_ID = DS4_QUESTION_ID,
    @iAnswer_CD = DS4_ANSWER_CD,
    @iScore_AMT = DS4_SCORE_AMT,
    @sResponse_Txt = DS4_RESPONSE_TXT,
    @iFile_ID = DS4_FILE_ID,
    @sFile_Name = DS4_FILE_NM,
    @sFile_Type_CD = DS4_FILE_TYPE_CD,
    @sFile_Size_Qty = DS4_FILE_SIZE_QTY,
    @Document_Data = DS4_DOCUMENT_DATA
    FROM Inserted

    --Check to see if there is already an entry in the response table, so to update only- the key fields
    IF EXISTS (SELECT 'X' FROM DST_QUEST_RESPONSE
    WHERE DS4_SURVEY_NBR = @iSurvey_NBR AND DS4_USER_ID = @sUser_ID AND DS4_QUESTION_ID = @iQuestion_ID)
    BEGIN
    --Row already exists, so update
    UPDATE DST_QUEST_RESPONSE
    SET DS4_ANSWER_CD = @iAnswer_CD,
    DS4_SCORE_AMT = @iScore_AMT,
    DS4_RESPONSE_TXT = @sResponse_Txt,
    DS4_FILE_ID = @iFile_ID,
    DS4_FILE_NM = @sFile_Name,
    DS4_FILE_TYPE_CD = @sFile_Type_CD,
    DS4_FILE_SIZE_QTY = @sFile_Size_Qty,
    DS4_DOCUMENT_DATA = @Document_Data

    WHERE DS4_SURVEY_NBR = @iSurvey_NBR AND DS4_USER_ID = @sUser_ID AND DS4_QUESTION_ID = @iQuestion_ID
    END
    Steve

  3. #3
    Join Date
    Jun 2003
    Posts
    5

    Talking

    Thanks,

    worked a treat...

Posting Permissions

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