Updating inserted row in Insert trigger

Updating inserted row in Insert trigger

Post by prefersgol » Sun, 26 Aug 2007 07:48:11


Can you update the inserted row from within the insert trigger?
 
 
 

Updating inserted row in Insert trigger

Post by Adam Macha » Sun, 26 Aug 2007 11:07:19

No :(


--

Adam Machanic
SQL Server MVP - http://www.yqcomputer.com/

Author, "Expert SQL Server 2005 Development"
http://www.yqcomputer.com/

 
 
 

Updating inserted row in Insert trigger

Post by Tibor Kara » Sun, 26 Aug 2007 16:51:55

I guess the answer depends on how we read the question...

You cannot modify data in the inserted and deleted tables.

You can modify the table that the trigger is created on.

--
Tibor Karaszi, SQL Server MVP
http://www.yqcomputer.com/
http://www.yqcomputer.com/
 
 
 

Updating inserted row in Insert trigger

Post by Adam Macha » Mon, 27 Aug 2007 01:09:02

True. But it would certainly be nice to be able to modify the
inserted/deleted tables directly -- they could act as a passthrough back to
the table, and perhaps in this case trigger recursion would be automatically
supressed? Would be an extremely useful feature...


--

Adam Machanic
SQL Server MVP - http://www.yqcomputer.com/

Author, "Expert SQL Server 2005 Development"
http://www.yqcomputer.com/



"Tibor Karaszi" < XXXX@XXXXX.COM > wrote in
 
 
 

Updating inserted row in Insert trigger

Post by Erland Som » Mon, 27 Aug 2007 07:37:30

Adam Machanic ( XXXX@XXXXX.COM ) writes:

Or a feature to permit you to shoot yourself in the foot...

I often copy inserted/deleted to table variables, in which case I'm
free to modify data or add extra columns.



--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Books Online for SQL Server 2005 at
http://www.yqcomputer.com/
Books Online for SQL Server 2000 at
http://www.yqcomputer.com/
 
 
 

Updating inserted row in Insert trigger

Post by Adam Macha » Mon, 27 Aug 2007 08:21:01


Yes, that too... But all of the most powerful features can end up
causing problems, so I don't see how that's an issue. You can shoot
yourself in the foot with query hints, CLR executables, improper indexes,
and any number of other features, including triggers themselves!

Anyway, I was thinking it would be nice for two situations in
particular: A) Hierarchies (materialized path or nested sets), and B)
Programmatic concurrency control schemes -- both of which I use triggers
for, and both of which require you to deal with recursion, either by
disabling it entirely or doing checks...


--

Adam Machanic
SQL Server MVP - http://www.yqcomputer.com/

Author, "Expert SQL Server 2005 Development"
http://www.yqcomputer.com/