Hi All,
Thanks in advance!
I want to generate an XML file in a Update trigger script.
Only the updated record needs to be exported to the XML file.
How do I know which record is being updated in the Update trigger?
Thank you so much!
Regards,
SeanSean,
Could be more than one record. Use the DELETED and INSERTED pseudo/logical
tables available within the firing of the trigger.
HTH
Jerry
"Sean" <ventilla@.hotmail.com> wrote in message
news:eZpeMm5zFHA.1924@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> Thanks in advance!
> I want to generate an XML file in a Update trigger script.
> Only the updated record needs to be exported to the XML file.
> How do I know which record is being updated in the Update trigger?
> Thank you so much!
>
> Regards,
> Sean
>|||Define being updated. If you mean the rows that were touched, even if all
values remain the same:
select *
from inserted
If you mean those that have actually changed:
select *
from inserted
join deleted
on <some unchangable value, like a surrogate (identity)
key>
where inserted.col1 <> deleted.col2 --not nullable
or (inserted.col2 <> deleted.col2
or (inserted.col2 is null and deleted.col2 is not null)
or (inserted.col2 is not null and deleted.col2 is null) )
If you mean something else, then please provide more info.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Sean" <ventilla@.hotmail.com> wrote in message
news:eZpeMm5zFHA.1924@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> Thanks in advance!
> I want to generate an XML file in a Update trigger script.
> Only the updated record needs to be exported to the XML file.
> How do I know which record is being updated in the Update trigger?
> Thank you so much!
>
> Regards,
> Sean
>|||Dear Louis,
I need all the records that have changed and inserted.
That means a modified record and new record.
For example:
CREATE TRIGGER trgUpdate
ON User_Master
FOR UPDATE
AS
DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)
SET @.FileName =
REPLACE('c:\result_'+CONVERT(char(8),GET
DATE(),1)+'.xml','/','-')
SET @.bcpCommand = 'bcp "SELECT * FROM KDMNN..User_Master Where UserID=' +
CONVERT(varchar(5),@.@.identity) + ' for xml raw" queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U sa -P -c'
EXEC master..xp_cmdshell @.bcpCommand
I have a problem detecting the @.@.identity. I could not get the information
of which records have been modified and inserted.
Thank you very much!
Regards,
Sean
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uCMwbb7zFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Define being updated. If you mean the rows that were touched, even if all
> values remain the same:
> select *
> from inserted
> If you mean those that have actually changed:
> select *
> from inserted
> join deleted
> on <some unchangable value, like a surrogate (identity)
> key>
> where inserted.col1 <> deleted.col2 --not nullable
> or (inserted.col2 <> deleted.col2
> or (inserted.col2 is null and deleted.col2 is not null)
> or (inserted.col2 is not null and deleted.col2 is null) )
> If you mean something else, then please provide more info.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "Sean" <ventilla@.hotmail.com> wrote in message
> news:eZpeMm5zFHA.1924@.TK2MSFTNGP14.phx.gbl...
>|||Yeah, you cannot rely on identities to identify a row like this. You really
need to have another key that you can expect not to change. Since you
insert the values, you know them and can trust them.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Sean" <ventilla@.hotmail.com> wrote in message
news:uTlSTNG0FHA.908@.tk2msftngp13.phx.gbl...
> Dear Louis,
> I need all the records that have changed and inserted.
> That means a modified record and new record.
> For example:
> CREATE TRIGGER trgUpdate
> ON User_Master
> FOR UPDATE
> AS
> DECLARE @.FileName varchar(50),
> @.bcpCommand varchar(2000)
> SET @.FileName =
> REPLACE('c:\result_'+CONVERT(char(8),GET
DATE(),1)+'.xml','/','-')
> SET @.bcpCommand = 'bcp "SELECT * FROM KDMNN..User_Master Where UserID=' +
> CONVERT(varchar(5),@.@.identity) + ' for xml raw" queryout "'
> SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U sa -P -c'
> EXEC master..xp_cmdshell @.bcpCommand
> I have a problem detecting the @.@.identity. I could not get the information
> of which records have been modified and inserted.
> Thank you very much!
>
> Regards,
> Sean
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uCMwbb7zFHA.2540@.TK2MSFTNGP09.phx.gbl...
>|||Dear Louis,
Thanks for you information.
It helps a lot!
I am trying to copy the modified or new data from User_Master into a
User_MasterTemp table using an Update trigger.
CREATE TRIGGER trgUpdate ON User_Master
FOR UPDATE
AS
Delete From User_MasterTemp
Insert into User_MasterTemp Select * From Inserted
GO
Then do a exporting to XML file from the temp table using an Insert trigger.
CREATE TRIGGER User_MasterInsert ON [dbo].[User_MasterTemp]
FOR INSERT
AS
DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)
SET @.FileName =
REPLACE('c:\result_'+CONVERT(char(8),GET
DATE(),1)+'.xml','/','-')
SET @.bcpCommand = 'bcp "SELECT * FROM KDMNN..User_MasterTemp for xml raw"
queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U sa -P -c'
EXEC master..xp_cmdshell @.bcpCommand
Hopefully, the code can work. I am doing the troubleshooting cause the
script in the Insert trigger hangs the SQL Server.
Regards,
Sean
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:O04yriG0FHA.916@.TK2MSFTNGP10.phx.gbl...
> Yeah, you cannot rely on identities to identify a row like this. You
> really need to have another key that you can expect not to change. Since
> you insert the values, you know them and can trust them.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "Sean" <ventilla@.hotmail.com> wrote in message
> news:uTlSTNG0FHA.908@.tk2msftngp13.phx.gbl...
>
Showing posts with label updated. Show all posts
Showing posts with label updated. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
How do I know if a column is updated?
Hi,
In SQL Server 2000, how do I know that a paricular column in a Table row is
updated?
e.g., in Customer(ID, Name, SSN, DoB, Address),
the (0001, John, 123456789,1980/01/02,'1234 Main Street'),
where the '1234 Main Street' is updated.
Thanks for help.
Jason
You could respond to the change in an UPDATE trigger. In the trigger
you can use the IF UPDATE()
<http://msdn.microsoft.com/library/de...es_08_7377.asp>
clause to determine if any particular column has been changed. For example,
CREATE TRIGGER MyUpdateTrigger ON dbo.MyTable
FOR UPDATE AS
IF UPDATE(MyCol)
BEGIN
...
END
GO
where MyCol is a column in dbo.MyTable that you want to respond to
changes on.
*mike hodgson*
http://sqlnerd.blogspot.com
Jason Huang wrote:
>Hi,
>In SQL Server 2000, how do I know that a paricular column in a Table row is
>updated?
>e.g., in Customer(ID, Name, SSN, DoB, Address),
>the (0001, John, 123456789,1980/01/02,'1234 Main Street'),
>where the '1234 Main Street' is updated.
>Thanks for help.
>
>Jason
>
>
In SQL Server 2000, how do I know that a paricular column in a Table row is
updated?
e.g., in Customer(ID, Name, SSN, DoB, Address),
the (0001, John, 123456789,1980/01/02,'1234 Main Street'),
where the '1234 Main Street' is updated.
Thanks for help.
Jason
You could respond to the change in an UPDATE trigger. In the trigger
you can use the IF UPDATE()
<http://msdn.microsoft.com/library/de...es_08_7377.asp>
clause to determine if any particular column has been changed. For example,
CREATE TRIGGER MyUpdateTrigger ON dbo.MyTable
FOR UPDATE AS
IF UPDATE(MyCol)
BEGIN
...
END
GO
where MyCol is a column in dbo.MyTable that you want to respond to
changes on.
*mike hodgson*
http://sqlnerd.blogspot.com
Jason Huang wrote:
>Hi,
>In SQL Server 2000, how do I know that a paricular column in a Table row is
>updated?
>e.g., in Customer(ID, Name, SSN, DoB, Address),
>the (0001, John, 123456789,1980/01/02,'1234 Main Street'),
>where the '1234 Main Street' is updated.
>Thanks for help.
>
>Jason
>
>
How do I know if a column is updated?
Hi,
In SQL Server 2000, how do I know that a paricular column in a Table row is
updated?
e.g., in Customer(ID, Name, SSN, DoB, Address),
the (0001, John, 123456789,1980/01/02,'1234 Main Street'),
where the '1234 Main Street' is updated.
Thanks for help.
JasonThis is a multi-part message in MIME format.
--020706030805090103030202
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
You could respond to the change in an UPDATE trigger. In the trigger
you can use the IF UPDATE()
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_7377.asp>
clause to determine if any particular column has been changed. For example,
CREATE TRIGGER MyUpdateTrigger ON dbo.MyTable
FOR UPDATE AS
IF UPDATE(MyCol)
BEGIN
...
END
GO
where MyCol is a column in dbo.MyTable that you want to respond to
changes on.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Jason Huang wrote:
>Hi,
>In SQL Server 2000, how do I know that a paricular column in a Table row is
>updated?
>e.g., in Customer(ID, Name, SSN, DoB, Address),
>the (0001, John, 123456789,1980/01/02,'1234 Main Street'),
>where the '1234 Main Street' is updated.
>Thanks for help.
>
>Jason
>
>
--020706030805090103030202
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>You could respond to the change in an UPDATE trigger. In the
trigger you can use the <a
href="http://links.10026.com/?link=IF">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_7377.asp">IF
UPDATE()</a> clause to determine if any particular column has been
changed. For example,<br>
</tt>
<blockquote><tt>CREATE TRIGGER MyUpdateTrigger ON dbo.MyTable</tt><br>
<tt>FOR UPDATE AS</tt><br>
<tt> IF UPDATE(MyCol)</tt><br>
<tt> BEGIN</tt><br>
<tt> ...</tt><br>
<tt> END</tt><br>
<tt>GO</tt><br>
</blockquote>
<tt>where MyCol is a column in dbo.MyTable that you want to respond to
changes on.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Jason Huang wrote:
<blockquote cite="midu8m7SusVGHA.5592@.TK2MSFTNGP09.phx.gbl" type="cite">
<pre wrap="">Hi,
In SQL Server 2000, how do I know that a paricular column in a Table row is
updated?
e.g., in Customer(ID, Name, SSN, DoB, Address),
the (0001, John, 123456789,1980/01/02,'1234 Main Street'),
where the '1234 Main Street' is updated.
Thanks for help.
Jason
</pre>
</blockquote>
</body>
</html>
--020706030805090103030202--
In SQL Server 2000, how do I know that a paricular column in a Table row is
updated?
e.g., in Customer(ID, Name, SSN, DoB, Address),
the (0001, John, 123456789,1980/01/02,'1234 Main Street'),
where the '1234 Main Street' is updated.
Thanks for help.
JasonThis is a multi-part message in MIME format.
--020706030805090103030202
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
You could respond to the change in an UPDATE trigger. In the trigger
you can use the IF UPDATE()
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_7377.asp>
clause to determine if any particular column has been changed. For example,
CREATE TRIGGER MyUpdateTrigger ON dbo.MyTable
FOR UPDATE AS
IF UPDATE(MyCol)
BEGIN
...
END
GO
where MyCol is a column in dbo.MyTable that you want to respond to
changes on.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Jason Huang wrote:
>Hi,
>In SQL Server 2000, how do I know that a paricular column in a Table row is
>updated?
>e.g., in Customer(ID, Name, SSN, DoB, Address),
>the (0001, John, 123456789,1980/01/02,'1234 Main Street'),
>where the '1234 Main Street' is updated.
>Thanks for help.
>
>Jason
>
>
--020706030805090103030202
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>You could respond to the change in an UPDATE trigger. In the
trigger you can use the <a
href="http://links.10026.com/?link=IF">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_7377.asp">IF
UPDATE()</a> clause to determine if any particular column has been
changed. For example,<br>
</tt>
<blockquote><tt>CREATE TRIGGER MyUpdateTrigger ON dbo.MyTable</tt><br>
<tt>FOR UPDATE AS</tt><br>
<tt> IF UPDATE(MyCol)</tt><br>
<tt> BEGIN</tt><br>
<tt> ...</tt><br>
<tt> END</tt><br>
<tt>GO</tt><br>
</blockquote>
<tt>where MyCol is a column in dbo.MyTable that you want to respond to
changes on.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Jason Huang wrote:
<blockquote cite="midu8m7SusVGHA.5592@.TK2MSFTNGP09.phx.gbl" type="cite">
<pre wrap="">Hi,
In SQL Server 2000, how do I know that a paricular column in a Table row is
updated?
e.g., in Customer(ID, Name, SSN, DoB, Address),
the (0001, John, 123456789,1980/01/02,'1234 Main Street'),
where the '1234 Main Street' is updated.
Thanks for help.
Jason
</pre>
</blockquote>
</body>
</html>
--020706030805090103030202--
How do I know if a column is updated?
Hi,
In SQL Server 2000, how do I know that a paricular column in a Table row is
updated?
e.g., in Customer(ID, Name, SSN, DoB, Address),
the (0001, John, 123456789,1980/01/02,'1234 Main Street'),
where the '1234 Main Street' is updated.
Thanks for help.
JasonYou could respond to the change in an UPDATE trigger. In the trigger
you can use the IF UPDATE()
<http://msdn.microsoft.com/library/d...
s_08_7377.asp>
clause to determine if any particular column has been changed. For example,
CREATE TRIGGER MyUpdateTrigger ON dbo.MyTable
FOR UPDATE AS
IF UPDATE(MyCol)
BEGIN
..
END
GO
where MyCol is a column in dbo.MyTable that you want to respond to
changes on.
*mike hodgson*
http://sqlnerd.blogspot.com
Jason Huang wrote:
>Hi,
>In SQL Server 2000, how do I know that a paricular column in a Table row is
>updated?
>e.g., in Customer(ID, Name, SSN, DoB, Address),
>the (0001, John, 123456789,1980/01/02,'1234 Main Street'),
>where the '1234 Main Street' is updated.
>Thanks for help.
>
>Jason
>
>
In SQL Server 2000, how do I know that a paricular column in a Table row is
updated?
e.g., in Customer(ID, Name, SSN, DoB, Address),
the (0001, John, 123456789,1980/01/02,'1234 Main Street'),
where the '1234 Main Street' is updated.
Thanks for help.
JasonYou could respond to the change in an UPDATE trigger. In the trigger
you can use the IF UPDATE()
<http://msdn.microsoft.com/library/d...
s_08_7377.asp>
clause to determine if any particular column has been changed. For example,
CREATE TRIGGER MyUpdateTrigger ON dbo.MyTable
FOR UPDATE AS
IF UPDATE(MyCol)
BEGIN
..
END
GO
where MyCol is a column in dbo.MyTable that you want to respond to
changes on.
*mike hodgson*
http://sqlnerd.blogspot.com
Jason Huang wrote:
>Hi,
>In SQL Server 2000, how do I know that a paricular column in a Table row is
>updated?
>e.g., in Customer(ID, Name, SSN, DoB, Address),
>the (0001, John, 123456789,1980/01/02,'1234 Main Street'),
>where the '1234 Main Street' is updated.
>Thanks for help.
>
>Jason
>
>
Subscribe to:
Posts (Atom)