Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #150 > unrolled thread

SSE 2008: Trigger: Modifying Data to be Written

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  SSE 2008: Trigger: Modifying Data to be Written Gene Wirchenko <genew@ocis.net> - 2011-04-08 15:11 -0700
    Re: SSE 2008: Trigger: Modifying Data to be Written "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2011-04-08 18:44 -0400
      Re: SSE 2008: Trigger: Modifying Data to be Written Gene Wirchenko <genew@ocis.net> - 2011-04-08 16:23 -0700
        Re: SSE 2008: Trigger: Modifying Data to be Written Erland Sommarskog <esquel@sommarskog.se> - 2011-04-09 11:22 +0200
          Re: SSE 2008: Trigger: Modifying Data to be Written Gene Wirchenko <genew@ocis.net> - 2011-04-09 21:29 -0700
            Re: SSE 2008: Trigger: Modifying Data to be Written Erland Sommarskog <esquel@sommarskog.se> - 2011-04-10 14:48 +0200
              Re: SSE 2008: Trigger: Modifying Data to be Written Gene Wirchenko <genew@ocis.net> - 2011-04-11 09:46 -0700
            Re: SSE 2008: Trigger: Modifying Data to be Written "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2011-04-10 08:54 -0400
              Re: SSE 2008: Trigger: Modifying Data to be Written Erland Sommarskog <esquel@sommarskog.se> - 2011-04-10 21:33 +0200
              Re: SSE 2008: Trigger: Modifying Data to be Written Gene Wirchenko <genew@ocis.net> - 2011-04-11 09:42 -0700
                Re: SSE 2008: Trigger: Modifying Data to be Written "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-04-11 14:55 -0400
                  Re: SSE 2008: Trigger: Modifying Data to be Written Gene Wirchenko <genew@ocis.net> - 2011-04-11 14:53 -0700
                    Re: SSE 2008: Trigger: Modifying Data to be Written "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2011-04-11 18:49 -0400

#150 — SSE 2008: Trigger: Modifying Data to be Written

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

[toc] | [next] | [standalone]

#151

Gene Wirchenko wrote:
> 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 

[toc] | [prev] | [next] | [standalone]

#152

On Fri, 8 Apr 2011 18:44:47 -0400, "Bob Barrows"
<reb01501@NOSPAMyahoo.com> wrote:

>Gene Wirchenko wrote:
>> Dear SQLers:
>>
>>     How do I modify the data that is going to be written to a table?

[snip]

>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

[toc] | [prev] | [next] | [standalone]

#154

Gene Wirchenko (genew@ocis.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.

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

[toc] | [prev] | [next] | [standalone]

#155

On Sat, 09 Apr 2011 11:22:07 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.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

>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

[toc] | [prev] | [next] | [standalone]

#156

Gene Wirchenko (genew@ocis.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.

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

[toc] | [prev] | [next] | [standalone]

#161

On Sun, 10 Apr 2011 14:48:15 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.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.

>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

[toc] | [prev] | [next] | [standalone]

#157

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. 

[toc] | [prev] | [next] | [standalone]

#158

Bob Barrows (reb01501@NOSPAMyahoo.com) writes:
> 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@sommarskog.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

[toc] | [prev] | [next] | [standalone]

#160

On Sun, 10 Apr 2011 08:54:48 -0400, "Bob Barrows"
<reb01501@NOSPAMyahoo.com> wrote:

>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

[toc] | [prev] | [next] | [standalone]

#162

Gene Wirchenko wrote:
> On Sun, 10 Apr 2011 08:54:48 -0400, "Bob Barrows"
> <reb01501@NOSPAMyahoo.com> wrote:
>
>> 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?
>
It is not a matter of wanting all the columns. It is a matter of writing
maintainable code that will not blow up just because a new column is added.
I disagree that listing all the columns makes code less maintainable.

An example where using selstar caused one of my colleagues to spend an
afternoon trying to fix a "bug" in his application occurred when he created
a view that used it, never expecting that he would have to recompile the
view to get it to return columns added after the initial compilation.

I've tested a trigger that uses selstar to select into a test table and it
does seem to behave as you want it to behave, not needing a recompile after
adding a column to the trigger table, so it should work as you desire
providing you solve the other problems Erland pointed out..

[toc] | [prev] | [next] | [standalone]

#163

On Mon, 11 Apr 2011 14:55:38 -0400, "Bob Barrows"
<reb01501@NOyahooSPAM.com> wrote:

[snip]

>It is not a matter of wanting all the columns. It is a matter of writing
>maintainable code that will not blow up just because a new column is added.

     Ironic.

     In your scenario, if a column is added and the trigger is not
modified, the table will not be properly updated because of the
missing column.

     In mine, the update will still work.

>I disagree that listing all the columns makes code less maintainable.

     It means that it is not obvious that an column was missed, and in
this case, one always wants all columns.

>An example where using selstar caused one of my colleagues to spend an
>afternoon trying to fix a "bug" in his application occurred when he created
>a view that used it, never expecting that he would have to recompile the
>view to get it to return columns added after the initial compilation.

     I have not used views yet.  Your point certainly is worth
considering there.  I do not disagree that selstar is usually a Bad
Thing, just that it is not always so.

>I've tested a trigger that uses selstar to select into a test table and it
>does seem to behave as you want it to behave, not needing a recompile after
>adding a column to the trigger table, so it should work as you desire
>providing you solve the other problems Erland pointed out..

     Yup.

     Thanks for your posts even if we are disagreeing somewhat.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#164

Gene Wirchenko wrote:
> On Mon, 11 Apr 2011 14:55:38 -0400, "Bob Barrows"
> <reb01501@NOyahooSPAM.com> wrote:
>
> [snip]
>
>> It is not a matter of wanting all the columns. It is a matter of
>> writing maintainable code that will not blow up just because a new
>> column is added.
>
>     Ironic.
>
>     In your scenario, if a column is added and the trigger is not
> modified, the table will not be properly updated because of the
> missing column.
>
But it will be expected. That's the difference. We will know upfront that 
the trigger has to be updated when the table's design changes. 

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web