Using a trigger to modify data.

Question

Using a trigger to modify data.

jim Etheridge

SSC-Addicted

Points: 430
More actions
April 16, 2009 at 3:37 pm

#129406

How can I use a trigger to modify data that is being inserted into a table the trigger references?
SET UP: I need to perform quality assurance on a column in a table by making sure inserted or updated values are the right "class".
SUGGESTED SOLUTION: Use an AFTER trigger to check the class of the inserted or updated column, and change it to the correct value if needed.
PROBLEM: What is the proper syntax to use in the trigger if I need to change the inserted or updated value? Will a simple update statement work?

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

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

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 1

You should use an instead-of trigger; you can simply read the information from the inserted pseudo-table and insert it into your target table with any necessary modifications.

A better way to approach this would be to use a stored proc instead of a trigger, thereby eliminating the trigger overhead - it does rely on all inserts to your target table being done through the proc though, and having the appropriate security in place to enforce that.

jim Etheridge SSC-Addicted Points: 430 More actions · Answer 2

Thanks, Matt. Have you actually tried using a trigger to change data in the table it references?

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 3

Here is an example. Your case may be as simple as using a case statement in the select clause or it may involve multiple statements, but ultimately your source will be the inserted table and your destination will be the base table.

create table test (id int, value int);

go

create trigger tr_test on test instead of insert as

begin

insert into test (id, value)

select id, value + 100

from inserted;

end;

go

insert into test (id, value) values (1, 1);

select * from test;

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

You can do this in an INSTEAD OF trigger as Matt mentions, but my preference is to use stored procedures for inserts and put this kind of validation in the stored procedure.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SQLRocker SSCrazy Points: 2217 More actions · Answer 5

Before you think about triggers,sps, think about using Constraints like Check & all, Get more info from BOL.