Post reply

Insert Trigger

  • August 19, 2004 at 3:22 pm

    #109384

    I need to calculate a value for a column based on the value from a column that is in the "Inserted" table. The problem that I am having is the column I need to change is on the table that has the trigger.

    So how do I change the value of a column in the trigger for that table.

    Example: Table1 has the following columns: C1, C2, C3

    In the Insert trigger on Table1 I want to do something like this.

    Update Table1

    set C1 = 'ValueChanged'

    Thanks for any help.

    Kris

  • August 19, 2004 at 7:56 pm

    #519926

    Not quite sure I understand what you're trying to do. You say that this is an INSERT trigger - ie a trigger which is run when a new record is added to a table, so field C1 would be NULL otherwise. If the calculation is fairly simple, why not just define C1 to be a calculated field?

    Or perhaps you mean an UPDATE trigger?

    Regards

    Phil

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • August 20, 2004 at 7:44 am

    #519985

    Hi Phil,

    The calculated field idea won't work because I don't want the value to change once it has been calculated. Basically I am creating a confirmation number for a record when it is being inserted into the table and it is based off the identity field on the table. We have a function that takes the identity and creates the confirmation number, and I want to set that on the insert of the record. Currently an Update statement is ran after the records have been insert so where the confirmation number is update on the table. I just want to have this done during the insert of the record. But I have not been able to determine if I can change a value from the "inserted" table that is being inserted into the table on which the trigger has been placed.

    Thanks,

    Kris

  • August 20, 2004 at 9:43 am

    #520013

    If the value is based on running a function against the identity field, you should be able to use a caclulated field.

    CREATE   FUNCTION [Add2] (@id INT)   

    RETURNS INT AS   

    BEGIN  

    RETURN(SELECT @id+2)  

    END 

    CREATE TABLE test (

     t_id   INT IDENTITY(1,1),

     Val    INT,

     CompValue  AS dbo.add2(t_id)

    )

    insert into test (val) select 44

    select * From test

    will return

    t_id        Val         CompValue  

    ----------- ----------- -----------

    1           44          3

    If the identity value will change for the order, then you can do an update statement within you trigger.

    CREATE TRIGGER tr_Test_CompValue ON [dbo].[Test]

    FOR INSERT

    AS

    IF (SELECT CompValue FROM Inserted) IS NULL

    UPDATE t

    SET t.CompValue=dbo.function(i.t_id) FROM Test t, Inserted i

    WHERE i.t_id=t.t_id

    Brian

  • August 20, 2004 at 9:55 am

    #520015

    The problem I have with using the calculated field is that the value will change if the formula that calculates the value is changed. So in my case if we change the way the function creates the confirmation number then all the previously created confirmation number will be re-calculated.

    I need to be able to have the field value calculated on an insert and then never have the value change unless it is updated directly.

    What I am doing currently is running an Update statement on the table to update the column I need the calculation performed on during the insert action. So within the Insert trigger I perform an Update on a column in the same table. I would like to avoid running the Update statement, but I have not been able to determine a way to get the desired action.

    Kris

  • August 20, 2004 at 10:43 am

    #520020

    /** This assumes that the Table1 consists of

    Conf_Num, Purch_Last_Name, ID_Col

    and that you building the confirmation number from the same table. **/

    CREATE TRIGGER Add_Confirmation_Num
 ON Table1
FOR UPDATE
AS IF INSERT ( Purch_Name ) 
  UPDATE Table1
   SET Conf_Num = (SELECT LEFT(INSERTED.Purch_Last_Name,5)
                    + RIGHT(INSERTED.ID_Col,5)
                    FROM Table1, INSERTED
        WHERE Table1.Purch_Last_Name 
            = INSERTED.Purch_Last_Name
        AND   Table1.ID_Col = INSERTED.ID_Col)
  WHERE Table1.Purch_Last_Name = INSERTED.Purch_Last_Name
  AND   Table1.ID_Col = INSERTED.ID_Col

    I think this is what you are looking for. Or at least points you in the non-wrong direction.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply