Trigger to Update Fields of Inserted Record

[MESLarochelle]

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

[Plamen Ratchev]

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.SQLStudio.com

[Hugo Kornelis]

On Fri, 19 Sep 2008 12:57:46 -0700 (PDT), ME***********@gmail.com wrote:

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

Hi Pete,

Plamen has already answered your question, but you should consider using
a derived column instead:

CREATE TABLE MyTable
(ID int NOT NULL IDENTITY PRIMARY KEY,
Integer1 int NOT NULL,
Integer2 AS Integer1 + 10);
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[--CELKO--]

>I am trying to update a field [sic] of a newly inserted record [sic] byusing an
insert trigger. *<<

Your whole mindset is wrong. Rows are not records, columns are not
fields, A table must have a key and IDENTITY cannot be a key by
definition. What you are designing is a file system in SQL.

The language is more abstract than the punch card approach you have.
Data elements do not have to be materialized like they did with punch
cards. This second column should be computed in a VIEW (or with a
computed column, if you want to use proprietary features).

Next, we hare triggers almost as much as cursors and other procedural
code, but you need to know about them. We had to use them in the old
days before CHECK() and DRI actions were in the language. You might
write as many as 4-5 of them in your entire career today, if you work
with really strange databases/data.

Unfortunately, the T-SQL version is very weak. In Standard SQL, they
can be BEFORE or AFTER triggers, while T-SQL only has the INSTEAD OF
trigger for before operations. That is why you were having problems
writing a kludge -- you want to do the wrong thing with a bad tool.

[shuurai11]

Unfortunately, the T-SQL version is very weak. *In Standard SQL, they
can be BEFORE or AFTER triggers, while T-SQL only has the INSTEAD OF
trigger for before operations. *

Wrong. T-SQL has both INSTEAD OF and AFTER triggers. Someone who
claims to be an expert should know this, and in any event, Plamen
gives you an example of one in his response above.

That is why you were having problems
writing a kludge *-- you want to do the wrong thing with a bad tool.

Maybe you ought to update your knowledge of the tool before giving
advice about it?

[--CELKO--]

>Wrong. *T-SQL has both INSTEAD OF and AFTER triggers. *<<

To quote myself:
"In Standard SQL, they can be BEFORE or AFTER triggers, while T-SQL
only has the INSTEAD OF trigger for before operations."

Is there an actual BEFORE trigger now? You might want to read what I
actually posted :)

Other than the standard objections to trigger-vs-declarations, I
dislike having only the INSTEAD OF option because we meant it to be
for VIEWs to avoid view update problems. This means that they will be
a little confusing to maintenance programmers and that any special
optimizations for view update will have to be checked for.