I have a table that has a field populated by single character data.
in it 'm' is different than 'M'. How do I differentiate this in my
queries...
It works automatically on my base SQL server, but not on the replicant.
Thanks for the help...Atley,
Please search the SQL Server 2000 Books Online re: "COLLATE" and
"COLLATIONS" to understand why you are getting different results on the
different SQL Server instances.
To help you out immediately, however, the following is an example of how to
use the COLLATE syntax within a SELECT:
create table Atley (c1 char (1) NOT NULL)
go
insert into Atley values ('m')
insert into Atley values ('M')
select *
from Atley
where c1 = 'm' collate Latin1_General_CS_AS
Chief Tenaya
"Atley" <atley_1@.homtmail.com> wrote in message
news:uwl89FFEEHA.3696@.TK2MSFTNGP10.phx.gbl...
> I have a table that has a field populated by single character data.
> in it 'm' is different than 'M'. How do I differentiate this in my
> queries...
> It works automatically on my base SQL server, but not on the replicant.
>
> Thanks for the help...
>
>|||As Tenaya says, this is related to collations. It seems that your collation
on the publisher is case-sensitive and this has replicated to the subscriber
as a case-insensitive collation. Tenaya's query will allow a case-sensitive
comparison to be done on case-insensitive data. However, if you want the
column collation itself to be transferred to the subscriber, so queries
don't need to be modified, then have a look at the article properties,
snapshot tab - there is a checkbox to select the collation there. You'll
need to reinitialize for this to take effect. BTW, the database collation on
the subscriber is the one you're inheriting, so modifying this (ALTER
DATABASE...) and reinitializing would give the same result.
HTH,
Paul Ibison
Showing posts with label single. Show all posts
Showing posts with label single. Show all posts
Thursday, March 22, 2012
Wednesday, March 7, 2012
Carriage Returns and Line Breaks
I have an address field that is coming back as a single field with carriage returns and line breaks and I would like to have it properly wrap in a single box, but the wrapping is all off. How can I get this to properly break? ThanksMake sure you have text box property "cangrow" to true.|||
Nope. That is not the issue. It is cangrow = true.
What I want is to take a field that comes out as:
John Smith CR LB 123 Main Street CR LB Anytown, MA 01888 CR LB USA
as
John Smith
123 Main Street
Anytown, MA 01888
USA
What I am getting is:
John Smith 123
Main Street Anytown,
MA 01888 USA
So I am looking how to read the CR and LBs and maybe replace them with BR tags, not sure.
|||Also... what is being returned to indicate the line break are char(13)s. I am recreating a report that I had lost and was able to resolve this at one time, but forget how I got around it.|||try select field1 + char(13) + char(10) + field2
Saturday, February 25, 2012
Capturing Invalid Records for XML Inserts
Greetings. I'm new to Xml inserts into sql server. I've used "OpenXML"
to conduct an insert for single rows. But, I have a case where I'd like
to use the bulk insert method and am wondering if there's a way to
capture invalid records from the bulk insert, while still inserting the
valid ones.
Thanks.
-ak
Using SqlXmlBulkload, your only option is to preprocess the Xml source with
XSLT to enforce your business rules or to load the data into temp tables
and post process.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad/
|||Thanks Andrew.
"Andrew Conrad" wrote:
> Using SqlXmlBulkload, your only option is to preprocess the Xml
source with
> XSLT to enforce your business rules or to load the data into temp
tables
> and post process.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad/
to conduct an insert for single rows. But, I have a case where I'd like
to use the bulk insert method and am wondering if there's a way to
capture invalid records from the bulk insert, while still inserting the
valid ones.
Thanks.
-ak
Using SqlXmlBulkload, your only option is to preprocess the Xml source with
XSLT to enforce your business rules or to load the data into temp tables
and post process.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad/
|||Thanks Andrew.
"Andrew Conrad" wrote:
> Using SqlXmlBulkload, your only option is to preprocess the Xml
source with
> XSLT to enforce your business rules or to load the data into temp
tables
> and post process.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad/
Capturing Invalid Records for XML Inserts
Greetings. I'm new to Xml inserts into sql server. I've used "OpenXML"
to conduct an insert for single rows. But, I have a case where I'd like
to use the bulk insert method and am wondering if there's a way to
capture invalid records from the bulk insert, while still inserting the
valid ones.
Thanks.
-akUsing SqlXmlBulkload, your only option is to preprocess the Xml source with
XSLT to enforce your business rules or to load the data into temp tables
and post process.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad/|||Thanks Andrew.
"Andrew Conrad" wrote:
> Using SqlXmlBulkload, your only option is to preprocess the Xml
source with
> XSLT to enforce your business rules or to load the data into temp
tables
> and post process.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad/
to conduct an insert for single rows. But, I have a case where I'd like
to use the bulk insert method and am wondering if there's a way to
capture invalid records from the bulk insert, while still inserting the
valid ones.
Thanks.
-akUsing SqlXmlBulkload, your only option is to preprocess the Xml source with
XSLT to enforce your business rules or to load the data into temp tables
and post process.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad/|||Thanks Andrew.
"Andrew Conrad" wrote:
> Using SqlXmlBulkload, your only option is to preprocess the Xml
source with
> XSLT to enforce your business rules or to load the data into temp
tables
> and post process.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad/
Sunday, February 19, 2012
Capture Before/After data on Multirow Updates
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
You can't assign multiple row values to a single variable. SQL just returns
the first row values -as you noticed.
For your test, change the code to
SELECT * FROM inserted
SELECT * FROM deleted
to see what is in the tables.
Provide more detail about what you are hoping to accomplish and we can help
you better.
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:CD971923-195E-464C-A51B-E447526EF7ED@.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
>
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
You can't assign multiple row values to a single variable. SQL just returns
the first row values -as you noticed.
For your test, change the code to
SELECT * FROM inserted
SELECT * FROM deleted
to see what is in the tables.
Provide more detail about what you are hoping to accomplish and we can help
you better.
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:CD971923-195E-464C-A51B-E447526EF7ED@.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
>
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
--
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
--
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
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
Subscribe to:
Posts (Atom)