Insert Trigger

Question

Insert Trigger

Kris Hahn

Mr or Mrs. 500

Points: 522
More actions
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

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

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

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Kris Hahn Mr or Mrs. 500 Points: 522 More actions · Answer 2

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

bdohmen SSCrazy Points: 2996 More actions · Answer 3

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

Kris Hahn Mr or Mrs. 500 Points: 522 More actions · Answer 4

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

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 5

/** 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.