SSE 2008: Trigger: Modifying Data to be Written

#1

Dear SQLers:

How do I modify the data that is going to be written to a table?

I understand that this data is in a logical table called
inserted, but that this data can not be modified. I really would like
to.

I want to put a filter in between to clean up the data. The
procedure that I posted recently that cleans up strings would be
useful here. I would like to invoke this in the trigger.

For example, with my table Accounts, if ACName is entered as
" Account Name "
I would like this to be cleaned up to
"Account Name"
and the latter value be the one written to the table.

Since I can not write to the inserted logical table, I do not
know how to do this.

One idea is to disallow application write access to Accounts,
have a shadow table that is written to and in its trigger, process its
rows and write accordingly to Accounts. This seems rather
complicated. I hope that there is a better way.

Sincerely,

Gene Wirchenko

#2

Gene Wirchenko wrote:

Quote:

Dear SQLers:

How do I modify the data that is going to be written to a table?

I understand that this data is in a logical table called
inserted, but that this data can not be modified. I really would like
to.

I want to put a filter in between to clean up the data. The
procedure that I posted recently that cleans up strings would be
useful here. I would like to invoke this in the trigger.

For example, with my table Accounts, if ACName is entered as
" Account Name "
I would like this to be cleaned up to
"Account Name"
and the latter value be the one written to the table.

Since I can not write to the inserted logical table, I do not
know how to do this.

One idea is to disallow application write access to Accounts,
have a shadow table that is written to and in its trigger, process its
rows and write accordingly to Accounts. This seems rather
complicated. I hope that there is a better way.

Sincerely,

You can use an INSTEAD OF trigger - see Books OnLine

#3

On Fri, 8 Apr 2011 18:44:47 -0400, "Bob Barrows"
<reb01501 (AT) NOSPAMyahoo (DOT) com> wrote:

Quote:

Gene Wirchenko wrote:
Dear SQLers:

How do I modify the data that is going to be written to a table?

[snip]

Quote:

You can use an INSTEAD OF trigger - see Books OnLine

Thank you. I found an example of INSTEAD OF myself shortly after
posting. Half the battle of learning SQL Server is knowing exactly
what to ask for, and sometimes, it is the tougher half!

Sincerely,

Gene Wirchenko

#4

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:

Quote:

Thank you. I found an example of INSTEAD OF myself shortly after
posting. Half the battle of learning SQL Server is knowing exactly
what to ask for, and sometimes, it is the tougher half!

I like to add that many developers would prefer to do this with an AFTER
trigger. This means a higher overhead, because the bad data is first
written to the table, and then you need to update the target table.
On the other hand, INSTEAD OF triggers gives you a maintainability
problem. Since you need to redo the original statement, you need to
update the trigger every time you add a column to the table. If you
forget you may get some headache sorting out what is going on.

What you really want is a BEFORE trigger where you can modify inserted.
But unfortunately SQL Server does not have BEFORE triggers.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

#5

On Sat, 09 Apr 2011 11:22:07 +0200, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Quote:

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
Thank you. I found an example of INSTEAD OF myself shortly after
posting. Half the battle of learning SQL Server is knowing exactly
what to ask for, and sometimes, it is the tougher half!

I like to add that many developers would prefer to do this with an AFTER
trigger. This means a higher overhead, because the bad data is first
written to the table, and then you need to update the target table.
On the other hand, INSTEAD OF triggers gives you a maintainability
problem. Since you need to redo the original statement, you need to
update the trigger every time you add a column to the table. If you
forget you may get some headache sorting out what is going on.

1) Part of adding a column would be considering whether it needs
this adjusting and so to add its handling to the trigger.

2) I think that maintainability was covered by the code I borrowed
from. My derivative has
select * into #Inserted from Inserted
at the beginning and then at the end, writes with
insert into Accounts select * from #Inserted

Quote:

What you really want is a BEFORE trigger where you can modify inserted.
But unfortunately SQL Server does not have BEFORE triggers.

Sincerely,

Gene Wirchenko

#6

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:

Quote:

2) I think that maintainability was covered by the code I borrowed
from. My derivative has
select * into #Inserted from Inserted
at the beginning and then at the end, writes with
insert into Accounts select * from #Inserted

That should work. One of the few situations where SELECT * in production
code could be defended.

Unfortunately, it will not work if the table has an identity column, a
timestamp column or a computed colunm.

I have also experienced performance problems with using SELECT INTO in a
trigger which was written to handle multi-row inserts, but the transaction
at hand was a loop that did things one-by-one. I found by testing that
SELECT INTO was more expensive that using CREATE TABLE #temp, which in its
turn was more expensive than table variables. Then again, this was on SQL
2000, and it is possible that the temp-table caching in later versions
alleviates the problem. I haven't come around to test that.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

#7

Gene Wirchenko wrote:

Quote:

2) I think that maintainability was covered by the code I borrowed
from. My derivative has
select * into #Inserted from Inserted
at the beginning and then at the end, writes with
insert into Accounts select * from #Inserted

You consider this to be a good idea? Despite the number of experts in the
field that advise against using selstar (select *)? I won't repeat that
advice here: it's very easily found. I will say that I have never seen an
expert recommend its use.

#8

Bob Barrows (reb01501 (AT) NOSPAMyahoo (DOT) com) writes:

Quote:

Gene Wirchenko wrote:
2) I think that maintainability was covered by the code I borrowed
from. My derivative has
select * into #Inserted from Inserted
at the beginning and then at the end, writes with
insert into Accounts select * from #Inserted

You consider this to be a good idea? Despite the number of experts in the
field that advise against using selstar (select *)? I won't repeat that
advice here: it's very easily found. I will say that I have never seen an
expert recommend its use.

As I indicated in my reply to Gene, I think he has a point here.

But as I said, there are couple of situations where it will not work. And I
forgot quite an important situation: UPDATE statements. Most likely, Gene
would like to use his INSTEAD OF trigger for updates as well, and you
cannot do UPDATE tbl SET CORRESPONDING as in Cobol. So for updates he would
have to list all columns.

....unless he first deletes the rows to be updated and the insert like above.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

#9

On Sun, 10 Apr 2011 08:54:48 -0400, "Bob Barrows"
<reb01501 (AT) NOSPAMyahoo (DOT) com> wrote:

Quote:

Gene Wirchenko wrote:
2) I think that maintainability was covered by the code I borrowed
from. My derivative has
select * into #Inserted from Inserted
at the beginning and then at the end, writes with
insert into Accounts select * from #Inserted

You consider this to be a good idea? Despite the number of experts in the
field that advise against using selstar (select *)? I won't repeat that
advice here: it's very easily found. I will say that I have never seen an
expert recommend its use.

Saying that something never has a valid use is a very broad
statement. It would be very easy for someone, even an expert, to miss
edge cases where it would be a good idea.

This is definitely a case where one wants all columns. Why make
maintenance that much more difficult by insisting on listing all of
the columns explicitly?

Sincerely,

Gene Wirchenko

#10

On Sun, 10 Apr 2011 14:48:15 +0200, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Quote:

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
2) I think that maintainability was covered by the code I borrowed
from. My derivative has
select * into #Inserted from Inserted
at the beginning and then at the end, writes with
insert into Accounts select * from #Inserted

That should work. One of the few situations where SELECT * in production
code could be defended.

Unfortunately, it will not work if the table has an identity column, a
timestamp column or a computed colunm.

Ouch! What should I do then? I will be pondering this. I am
working to come up with as general a solution as possible to data
cleaning and validation.

Quote:

I have also experienced performance problems with using SELECT INTO in a
trigger which was written to handle multi-row inserts, but the transaction
at hand was a loop that did things one-by-one. I found by testing that
SELECT INTO was more expensive that using CREATE TABLE #temp, which in its
turn was more expensive than table variables. Then again, this was on SQL
2000, and it is possible that the temp-table caching in later versions
alleviates the problem. I haven't come around to test that.

I have to consider how to handle multi-row operations, too.

There is no shortage of things to learn. One of these things is
learning what I have to learn.

Sincerely,

Gene Wirchenko