uninstall SQL: updates

Showing posts with label updates. Show all posts

Monday, March 19, 2012

Cascading updates question

I have two tables, Stock and Positions. Stock contains a Symbol column and a
Price column, and Symbol is the key. Positions contain the columns as well a
s
several other columns whose data should change with the Price. Positions can
have the same Symbol multiple times (it is keyed by Symbol-Account).
My design is to have a foreign key between the tables, so that when the
Price in the Stock column is updated, the Price in Positions also updates. I
am making some columns in the Positions table computed columns, so that they
recalculate when the Price updates.
My questions/concerns are:
1. Is this design any good? One alternative I was thinkning of was to use no
computed columns, and run a stored procedure frequently to update the
Positions.
2. Will the computed columns updated automatically when Price changes?
3. Will there be the possibility that some rows in Positions with the same
symbols are not updated simultaneously, so that the Price could be different
for the same symbol?
4. Do the rows lock while they are updating? This has implications because I
am querying this table often for other purposes.
5. If one computed column depends on another computed column, is there a way
to specify the order in which they calculate, or is this just a big no-no?
I am grateful for any insight.
Thank you,
CP Developer"CP Developer" <steved@.newsgroup.nospam> wrote in message
news:A40102EA-6AF1-4377-9332-09EFCAA83204@.microsoft.com...
>I have two tables, Stock and Positions. Stock contains a Symbol column and
>a
> Price column, and Symbol is the key. Positions contain the columns as well
> as
> several other columns whose data should change with the Price. Positions
> can
> have the same Symbol multiple times (it is keyed by Symbol-Account).
> My design is to have a foreign key between the tables, so that when the
> Price in the Stock column is updated, the Price in Positions also updates.
> I
> am making some columns in the Positions table computed columns, so that
> they
> recalculate when the Price updates.
> My questions/concerns are:
> 1. Is this design any good? One alternative I was thinkning of was to use
> no
> computed columns, and run a stored procedure frequently to update the
> Positions.
There are a few reasonable ways I can think of to have a comupted column
based on a column in a related table.
Put a phoney foreign key on (StockID, Price) and use cascade updates (your
idea).
Put a trigger on Stock to update the related positions.
Use a view to join the two tables and define the calculations there.

> 2. Will the computed columns updated automatically when Price changes?

> 3. Will there be the possibility that some rows in Positions with the same
> symbols are not updated simultaneously, so that the Price could be
> different
> for the same symbol?
No.

> 4. Do the rows lock while they are updating? This has implications because
> I
> am querying this table often for other purposes.
Yes. Make sure you have an index supporting the foreign key relationship.

> 5. If one computed column depends on another computed column, is there a
> way
> to specify the order in which they calculate, or is this just a big no-no?
>
No you cannot base one computed column on another. However you are free to
cut and paste the calculation for one column into the other.
Here's an example:
drop table position
drop table stock
go
create table stock
(
id int primary key,
price decimal(9,2),
constraint uk_id_price
unique (id,price)
)
create table position
(
account int not null, -- references account
stock int not null references stock,
price decimal(9,2) not null,
other_price as cast(price*.9 as decimal(9,2)),
constraint pk_position
primary key(account,stock),
constraint fk_position_stock_price
foreign key (stock,price)
references stock(id,price)
on update cascade
)
create index ix_position_stock_price
on position(stock,price)
go
insert into stock(id,price) values (1,3.50)
insert into position(account,stock,price) values (23,1,3.50)
go
update stock set price = 5.25 where id = 1
select * from position
David|||The columns will update when the price changes.
Create Table TableA
(
Symbol varchar(10),
Price Money,
Qty Int,
TotalCost As (Price * Qty)
)
Insert Into TableA
Select 'GBP', 14.52, 2
select * From TableA
Update TableA
Set Qty = 151, price = 45.65
select * From TableA
Drop table TableA
HTH
Barry|||I would store the price in one table only, and select it from there. If
you need you selects to be as fast as possible, consider using an
indexed view.

Cascading Updates / Delete Problem

I have 3 tables set up in the typical customer - order - order item setup. I
have cascading updates & deletes on the customer and order tables. Primary &
secondary keys are correct.
When I delete, or update the customer table key field, it takes about 60
seconds to cascade to the child tables. If I execute manually the delete
operation startting at the order item table, then the orders, and finally the
customer table it only takes a second.
Why is the cascading taking so long, when doing the same task manually takes
a fraction of the time?If you do the manual delete, do you have the cascading foreign leys in place
or not? If you don't, you can probably fix the issue by creating indexes on
the foreign key columns.
--
Jacco Schalkwijk
SQL Server MVP
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>I have 3 tables set up in the typical customer - order - order item setup.
>I
> have cascading updates & deletes on the customer and order tables. Primary
> &
> secondary keys are correct.
> When I delete, or update the customer table key field, it takes about 60
> seconds to cascade to the child tables. If I execute manually the delete
> operation startting at the order item table, then the orders, and finally
> the
> customer table it only takes a second.
> Why is the cascading taking so long, when doing the same task manually
> takes
> a fraction of the time?|||All keys are in place.
"Jacco Schalkwijk" wrote:
> If you do the manual delete, do you have the cascading foreign leys in place
> or not? If you don't, you can probably fix the issue by creating indexes on
> the foreign key columns.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
> news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
> >I have 3 tables set up in the typical customer - order - order item setup.
> >I
> > have cascading updates & deletes on the customer and order tables. Primary
> > &
> > secondary keys are correct.
> > When I delete, or update the customer table key field, it takes about 60
> > seconds to cascade to the child tables. If I execute manually the delete
> > operation startting at the order item table, then the orders, and finally
> > the
> > customer table it only takes a second.
> > Why is the cascading taking so long, when doing the same task manually
> > takes
> > a fraction of the time?
>
>|||"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...
> All keys are in place.
>
Then you'll need to post a repro.
David|||What about indexes on the foreign key columns?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...
> All keys are in place.
> "Jacco Schalkwijk" wrote:
>> If you do the manual delete, do you have the cascading foreign leys in place
>> or not? If you don't, you can probably fix the issue by creating indexes on
>> the foreign key columns.
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
>> news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>> >I have 3 tables set up in the typical customer - order - order item setup.
>> >I
>> > have cascading updates & deletes on the customer and order tables. Primary
>> > &
>> > secondary keys are correct.
>> > When I delete, or update the customer table key field, it takes about 60
>> > seconds to cascade to the child tables. If I execute manually the delete
>> > operation startting at the order item table, then the orders, and finally
>> > the
>> > customer table it only takes a second.
>> > Why is the cascading taking so long, when doing the same task manually
>> > takes
>> > a fraction of the time?
>>

Cascading Updates / Delete Problem

Thursday, March 8, 2012

Cascade updates

I ran a bulk update on a SQL database for a range between dateswhere there
were trigger and possibly foreign keys. the parent updated fine but it
changed multiple child records although they were not relatedHi
Can you show us DDL + sample data + your query?
"Dangerfield" <Dangerfield@.discussions.microsoft.com> wrote in message
news:2E57BC94-A3B0-46CA-9349-C2DEB7C79F46@.microsoft.com...
> I ran a bulk update on a SQL database for a range between dateswhere there
> were trigger and possibly foreign keys. the parent updated fine but it
> changed multiple child records although they were not related

cascade deletes and updates

Hi,
I was wondering whether cascaded deletes or updates would be
replicated to the subscriber. The subscriber tables are just plain
tables without any constraints. But the publisher tables have FK
relationships. On the publishers database, if I deleted a row from the
parent table resulting in the removal of the child rows, would the
child rows also get removed in the subscriber tables? Thanks
- Komp
If the child row table is replicated then it'll work as you propose.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||By default they are replicated. If you don't want this behavior right click
on your table in EM, select Design Table, and then select the properties
icon (looks like a hand pointing to something) and click on relationships.
Ensure that Enforce Relationship for Inserts and Updates is selected and
check cascading Update Related Fields.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<k0mput3r@.gmail.com> wrote in message
news:1140023314.624365.105580@.o13g2000cwo.googlegr oups.com...
> Hi,
> I was wondering whether cascaded deletes or updates would be
> replicated to the subscriber. The subscriber tables are just plain
> tables without any constraints. But the publisher tables have FK
> relationships. On the publishers database, if I deleted a row from the
> parent table resulting in the removal of the child rows, would the
> child rows also get removed in the subscriber tables? Thanks
> - Komp
>
|||Also ensure that the enforce relationship for replication is enabled as
well.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e9R8BllMGHA.2036@.TK2MSFTNGP14.phx.gbl...
> By default they are replicated. If you don't want this behavior right
> click on your table in EM, select Design Table, and then select the
> properties icon (looks like a hand pointing to something) and click on
> relationships. Ensure that Enforce Relationship for Inserts and Updates is
> selected and check cascading Update Related Fields.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <k0mput3r@.gmail.com> wrote in message
> news:1140023314.624365.105580@.o13g2000cwo.googlegr oups.com...
>

Sunday, February 19, 2012

Capture Before/After data on Multirow Updates

Capture Before/After Data from Multirow Update

Problem: I only get one record of before and after data when performing
multirow updates from a single update statement. I want to get before and
after data for ALL updated records from the update statement. How can I do
this? I am using sp_trace_generateevent to capture before and after data i
n
a trace when performing updates using the following code:
CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR R
EPLICATION AS
BEGIN
PRINT 'HERE'
Declare @.mval nvarchar(256)
Declare @.mval2 nvarchar(256)
Declare @.mvalall nvarchar(512)
SELECT
@.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
' + ' Last Name: ' + au_lname + ' | '
FROM DELETED
SELECT
@.mval2 = ' After First Name: ' + au_fname + ' - ' + '
Last Name: ' + au_lname + ' | '
FROM INSERTED
Set @.mvalall=@.mval + @.mval2
EXEC sp_trace_generateevent
@.event_class = 82, @.userinfo=@.mvalall
ENDYmerejtrebor wrote:
> Problem: I only get one record of before and after data when performing
> multirow updates from a single update statement. I want to get before and
> after data for ALL updated records from the update statement. How can I d
o
> this? I am using sp_trace_generateevent to capture before and after data
in
> a trace when performing updates using the following code:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
REPLICATION AS
> BEGIN
> PRINT 'HERE'
> Declare @.mval nvarchar(256)
> Declare @.mval2 nvarchar(256)
> Declare @.mvalall nvarchar(512)
> SELECT
> @.mval = ' UPDATE: Before First Name: ' + au_fname + '
-
> ' + ' Last Name: ' + au_lname + ' | '
> FROM DELETED
> SELECT
> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
> Last Name: ' + au_lname + ' | '
> FROM INSERTED
> Set @.mvalall=@.mval + @.mval2
> EXEC sp_trace_generateevent
> @.event_class = 82, @.userinfo=@.mvalall
> END
How about inserting direct to a table. This example assumes no nullable
columns are involved and that the key is key_col:
CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
REPLICATION AS
BEGIN
INSERT INTO tracetable
(before_au_fname, before_au_lname, after_au_fname, after_au_lname)
SELECT D.au_fname, D.au_lname, I.au_fname, I.au_lname
FROM Inserted AS I
JOIN Deleted AS D
ON I.key_col = D.key_col
WHERE I.au_fname<>D.au_fname
OR I.au_lname<>D.au_lname ;
END
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> How about inserting direct to a table. This example assumes no nullable
> columns are involved and that the key is key_col:
And also assumes that the primary key value is not changed. I don't think
there is a way to correlate the before/after values for a multi-row update
in that case but one could perform a FULL OUTER JOIN instead of an INNER
JOIN to make sure all changes are recorded (but will NULL before or after
values).
Hope this helps.
Dan Guzman
SQL Server MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1161383771.558910.172880@.i42g2000cwa.googlegroups.com...
> Ymerejtrebor wrote:
>
> How about inserting direct to a table. This example assumes no nullable
> columns are involved and that the key is key_col:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
> REPLICATION AS
> BEGIN
> INSERT INTO tracetable
> (before_au_fname, before_au_lname, after_au_fname, after_au_lname)
> SELECT D.au_fname, D.au_lname, I.au_fname, I.au_lname
> FROM Inserted AS I
> JOIN Deleted AS D
> ON I.key_col = D.key_col
> WHERE I.au_fname<>D.au_fname
> OR I.au_lname<>D.au_lname ;
> END
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Ymerejtrebor" <Ymerejtrebor@.discussions.microsoft.com> wrote in message
news:B05C6CDD-C085-462C-98C0-1202A26F48CF@.microsoft.com...
> Problem: I only get one record of before and after data when performing
> multirow updates from a single update statement. I want to get before and
> after data for ALL updated records from the update statement. How can I
> do
> this? I am using sp_trace_generateevent to capture before and after data
> in
> a trace when performing updates using the following code:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
REPLICATION
> AS
> BEGIN
> PRINT 'HERE'
> Declare @.mval nvarchar(256)
> Declare @.mval2 nvarchar(256)
> Declare @.mvalall nvarchar(512)
> SELECT
> @.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
> ' + ' Last Name: ' + au_lname + ' | '
> FROM DELETED
> SELECT
> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
> Last Name: ' + au_lname + ' | '
> FROM INSERTED
> Set @.mvalall=@.mval + @.mval2
> EXEC sp_trace_generateevent
> @.event_class = 82, @.userinfo=@.mvalall
> END
>|||Dan Guzman wrote:
> And also assumes that the primary key value is not changed. I don't think
> there is a way to correlate the before/after values for a multi-row update
> in that case but one could perform a FULL OUTER JOIN instead of an INNER
> JOIN to make sure all changes are recorded (but will NULL before or after
> values).
>
Unless there is a key value (not necessarily the primary key) that
remains unchanged then it isn't possible to correlate row values before
and after the update. That is logical enough. Rows are identifiable
only by their keys so it is all but meaningless to talk of a row
changing its key value.
Some DBMSs do allow the before and after values to be correlated
without a key. Oracle for example has a FOR EACH ROW trigger that does
exactly that. An unfortunate side effect may be that the end result
depends on some internal physical state that isn't fully exposed
anywhere in the logical model. The real solution is to ensure that
every update operation preserves enough information to make the logical
meaning explicit. If you do that then key change should not be any
problem at all.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Capture Before/After Data from Multirow Update

Problem: I only get one record of before and after data when performing
multirow updates from a single update statement. I want to get before and
after data for ALL updated records from the update statement. How can I do
this? I am using sp_trace_generateevent to capture before and after data in
a trace when performing updates using the following code:
CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR REPLICATION AS
BEGIN
PRINT 'HERE'
Declare @.mval nvarchar(256)
Declare @.mval2 nvarchar(256)
Declare @.mvalall nvarchar(512)
SELECT
@.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
' + ' Last Name: ' + au_lname + ' | '
FROM DELETED
SELECT
@.mval2 = ' After First Name: ' + au_fname + ' - ' + '
Last Name: ' + au_lname + ' | '
FROM INSERTED
Set @.mvalall=@.mval + @.mval2
EXEC sp_trace_generateevent
@.event_class = 82, @.userinfo=@.mvalall
ENDYmerejtrebor wrote:
> Problem: I only get one record of before and after data when performing
> multirow updates from a single update statement. I want to get before and
> after data for ALL updated records from the update statement. How can I do
> this? I am using sp_trace_generateevent to capture before and after data in
> a trace when performing updates using the following code:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR REPLICATION AS
> BEGIN
> PRINT 'HERE'
> Declare @.mval nvarchar(256)
> Declare @.mval2 nvarchar(256)
> Declare @.mvalall nvarchar(512)
> SELECT
> @.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
> ' + ' Last Name: ' + au_lname + ' | '
> FROM DELETED
> SELECT
> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
> Last Name: ' + au_lname + ' | '
> FROM INSERTED
> Set @.mvalall=@.mval + @.mval2
> EXEC sp_trace_generateevent
> @.event_class = 82, @.userinfo=@.mvalall
> END
How about inserting direct to a table. This example assumes no nullable
columns are involved and that the key is key_col:
CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
REPLICATION AS
BEGIN
INSERT INTO tracetable
(before_au_fname, before_au_lname, after_au_fname, after_au_lname)
SELECT D.au_fname, D.au_lname, I.au_fname, I.au_lname
FROM Inserted AS I
JOIN Deleted AS D
ON I.key_col = D.key_col
WHERE I.au_fname<>D.au_fname
OR I.au_lname<>D.au_lname ;
END
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> How about inserting direct to a table. This example assumes no nullable
> columns are involved and that the key is key_col:
And also assumes that the primary key value is not changed. I don't think
there is a way to correlate the before/after values for a multi-row update
in that case but one could perform a FULL OUTER JOIN instead of an INNER
JOIN to make sure all changes are recorded (but will NULL before or after
values).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1161383771.558910.172880@.i42g2000cwa.googlegroups.com...
> Ymerejtrebor wrote:
>> Problem: I only get one record of before and after data when performing
>> multirow updates from a single update statement. I want to get before
>> and
>> after data for ALL updated records from the update statement. How can I
>> do
>> this? I am using sp_trace_generateevent to capture before and after
>> data in
>> a trace when performing updates using the following code:
>> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
>> REPLICATION AS
>> BEGIN
>> PRINT 'HERE'
>> Declare @.mval nvarchar(256)
>> Declare @.mval2 nvarchar(256)
>> Declare @.mvalall nvarchar(512)
>> SELECT
>> @.mval = ' UPDATE: Before First Name: ' + au_fname +
>> ' -
>> ' + ' Last Name: ' + au_lname + ' | '
>> FROM DELETED
>> SELECT
>> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
>> Last Name: ' + au_lname + ' | '
>> FROM INSERTED
>> Set @.mvalall=@.mval + @.mval2
>> EXEC sp_trace_generateevent
>> @.event_class = 82, @.userinfo=@.mvalall
>> END
>
> How about inserting direct to a table. This example assumes no nullable
> columns are involved and that the key is key_col:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
> REPLICATION AS
> BEGIN
> INSERT INTO tracetable
> (before_au_fname, before_au_lname, after_au_fname, after_au_lname)
> SELECT D.au_fname, D.au_lname, I.au_fname, I.au_lname
> FROM Inserted AS I
> JOIN Deleted AS D
> ON I.key_col = D.key_col
> WHERE I.au_fname<>D.au_fname
> OR I.au_lname<>D.au_lname ;
> END
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Ymerejtrebor" <Ymerejtrebor@.discussions.microsoft.com> wrote in message
news:B05C6CDD-C085-462C-98C0-1202A26F48CF@.microsoft.com...
> Problem: I only get one record of before and after data when performing
> multirow updates from a single update statement. I want to get before and
> after data for ALL updated records from the update statement. How can I
> do
> this? I am using sp_trace_generateevent to capture before and after data
> in
> a trace when performing updates using the following code:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR REPLICATION
> AS
> BEGIN
> PRINT 'HERE'
> Declare @.mval nvarchar(256)
> Declare @.mval2 nvarchar(256)
> Declare @.mvalall nvarchar(512)
> SELECT
> @.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
> ' + ' Last Name: ' + au_lname + ' | '
> FROM DELETED
> SELECT
> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
> Last Name: ' + au_lname + ' | '
> FROM INSERTED
> Set @.mvalall=@.mval + @.mval2
> EXEC sp_trace_generateevent
> @.event_class = 82, @.userinfo=@.mvalall
> END
>|||Dan Guzman wrote:
> > How about inserting direct to a table. This example assumes no nullable
> > columns are involved and that the key is key_col:
> And also assumes that the primary key value is not changed. I don't think
> there is a way to correlate the before/after values for a multi-row update
> in that case but one could perform a FULL OUTER JOIN instead of an INNER
> JOIN to make sure all changes are recorded (but will NULL before or after
> values).
>
Unless there is a key value (not necessarily the primary key) that
remains unchanged then it isn't possible to correlate row values before
and after the update. That is logical enough. Rows are identifiable
only by their keys so it is all but meaningless to talk of a row
changing its key value.
Some DBMSs do allow the before and after values to be correlated
without a key. Oracle for example has a FOR EACH ROW trigger that does
exactly that. An unfortunate side effect may be that the end result
depends on some internal physical state that isn't fully exposed
anywhere in the logical model. The real solution is to ensure that
every update operation preserves enough information to make the logical
meaning explicit. If you do that then key change should not be any
problem at all.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Capture Before/After Data from Multirow Update

Problem: I only get one record of before and after data when performing
multirow updates from a single update statement. I want to get before and
after data for ALL updated records from the update statement. How can I do
this? I am using sp_trace_generateevent to capture before and after data in
a trace when performing updates using the following code:
CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR REPLICATION AS
BEGIN
PRINT 'HERE'
Declare @.mval nvarchar(256)
Declare @.mval2 nvarchar(256)
Declare @.mvalall nvarchar(512)
SELECT
@.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
' + ' Last Name: ' + au_lname + ' | '
FROM DELETED
SELECT
@.mval2 = ' After First Name: ' + au_fname + ' - ' + '
Last Name: ' + au_lname + ' | '
FROM INSERTED
Set @.mvalall=@.mval + @.mval2
EXEC sp_trace_generateevent
@.event_class = 82, @.userinfo=@.mvalall
END
Ymerejtrebor wrote:
> Problem: I only get one record of before and after data when performing
> multirow updates from a single update statement. I want to get before and
> after data for ALL updated records from the update statement. How can I do
> this? I am using sp_trace_generateevent to capture before and after data in
> a trace when performing updates using the following code:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR REPLICATION AS
> BEGIN
> PRINT 'HERE'
> Declare @.mval nvarchar(256)
> Declare @.mval2 nvarchar(256)
> Declare @.mvalall nvarchar(512)
> SELECT
> @.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
> ' + ' Last Name: ' + au_lname + ' | '
> FROM DELETED
> SELECT
> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
> Last Name: ' + au_lname + ' | '
> FROM INSERTED
> Set @.mvalall=@.mval + @.mval2
> EXEC sp_trace_generateevent
> @.event_class = 82, @.userinfo=@.mvalall
> END
How about inserting direct to a table. This example assumes no nullable
columns are involved and that the key is key_col:
CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
REPLICATION AS
BEGIN
INSERT INTO tracetable
(before_au_fname, before_au_lname, after_au_fname, after_au_lname)
SELECT D.au_fname, D.au_lname, I.au_fname, I.au_lname
FROM Inserted AS I
JOIN Deleted AS D
ON I.key_col = D.key_col
WHERE I.au_fname<>D.au_fname
OR I.au_lname<>D.au_lname ;
END
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||> How about inserting direct to a table. This example assumes no nullable
> columns are involved and that the key is key_col:
And also assumes that the primary key value is not changed. I don't think
there is a way to correlate the before/after values for a multi-row update
in that case but one could perform a FULL OUTER JOIN instead of an INNER
JOIN to make sure all changes are recorded (but will NULL before or after
values).
Hope this helps.
Dan Guzman
SQL Server MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1161383771.558910.172880@.i42g2000cwa.googlegr oups.com...
> Ymerejtrebor wrote:
>
> How about inserting direct to a table. This example assumes no nullable
> columns are involved and that the key is key_col:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
> REPLICATION AS
> BEGIN
> INSERT INTO tracetable
> (before_au_fname, before_au_lname, after_au_fname, after_au_lname)
> SELECT D.au_fname, D.au_lname, I.au_fname, I.au_lname
> FROM Inserted AS I
> JOIN Deleted AS D
> ON I.key_col = D.key_col
> WHERE I.au_fname<>D.au_fname
> OR I.au_lname<>D.au_lname ;
> END
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Ymerejtrebor" <Ymerejtrebor@.discussions.microsoft.com> wrote in message
news:B05C6CDD-C085-462C-98C0-1202A26F48CF@.microsoft.com...
> Problem: I only get one record of before and after data when performing
> multirow updates from a single update statement. I want to get before and
> after data for ALL updated records from the update statement. How can I
> do
> this? I am using sp_trace_generateevent to capture before and after data
> in
> a trace when performing updates using the following code:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR REPLICATION
> AS
> BEGIN
> PRINT 'HERE'
> Declare @.mval nvarchar(256)
> Declare @.mval2 nvarchar(256)
> Declare @.mvalall nvarchar(512)
> SELECT
> @.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
> ' + ' Last Name: ' + au_lname + ' | '
> FROM DELETED
> SELECT
> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
> Last Name: ' + au_lname + ' | '
> FROM INSERTED
> Set @.mvalall=@.mval + @.mval2
> EXEC sp_trace_generateevent
> @.event_class = 82, @.userinfo=@.mvalall
> END
>
|||Dan Guzman wrote:
> And also assumes that the primary key value is not changed. I don't think
> there is a way to correlate the before/after values for a multi-row update
> in that case but one could perform a FULL OUTER JOIN instead of an INNER
> JOIN to make sure all changes are recorded (but will NULL before or after
> values).
>
Unless there is a key value (not necessarily the primary key) that
remains unchanged then it isn't possible to correlate row values before
and after the update. That is logical enough. Rows are identifiable
only by their keys so it is all but meaningless to talk of a row
changing its key value.
Some DBMSs do allow the before and after values to be correlated
without a key. Oracle for example has a FOR EACH ROW trigger that does
exactly that. An unfortunate side effect may be that the end result
depends on some internal physical state that isn't fully exposed
anywhere in the logical model. The real solution is to ensure that
every update operation preserves enough information to make the logical
meaning explicit. If you do that then key change should not be any
problem at all.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Monday, March 19, 2012

Cascading updates question

Cascading Updates / Delete Problem

Cascading Updates / Delete Problem

Cascading Updates / Delete Problem

Thursday, March 8, 2012

Cascade updates

cascade deletes and updates

Sunday, February 19, 2012

Capture Before/After data on Multirow Updates

Capture Before/After Data from Multirow Update

Capture Before/After Data from Multirow Update

Capture Before/After Data from Multirow Update

uninstall SQL

Blog Archive

About Me