Insert Trigger Update Field of Inserted Record

Insert Trigger Update Field of Inserted Record

Post by MESLaroche » Sun, 21 Sep 2008 04:58:53


I am trying to update a field of a newly inserted record by using an
insert trigger. For example:

Table has 3 fields:


MyTable:
ID (int, Identity), Integer1(int), Integer2(int)


INSERT INTO MyTable (Integer1) VALUES (20)


And then on the trigger have the Integer2 column be set to Integer1 +
10. Everything I have seen mentions that an Insert trigger can not
modify the data that has just been inserted. Can anyone help me with
this? I would be glad to clarify if needed.


Thanks,
Pete
 
 
 

Insert Trigger Update Field of Inserted Record

Post by Plamen Rat » Sun, 21 Sep 2008 05:33:06

Yes, you can do that. Here is example:

CREATE TABLE Foo (
keycol INT PRIMARY KEY,
col1 INT,
col2 INT);

GO

CREATE TRIGGER Foo$Insert
ON Foo
AFTER INSERT
AS
UPDATE Foo
SET col2 = I.col1 + 10
FROM Foo AS F
JOIN Inserted AS I
ON F.keycol = I.keycol;

GO

INSERT INTO Foo (keycol, col1) VALUES (1, 20);

SELECT keycol, col1, col2
FROM Foo;

--
Plamen Ratchev
http://www.yqcomputer.com/