How do I know which record is being updated in Update trigger?

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

How do I identify the replication generated column(s) in a table

I need to dynamically generate a select command that does not include
replication generated columns. Is there a way to do this? I could use
GetOleDbSchemaTable and filter for column names with "rowguid" but that does
not seem robust to me. Is there a system table or stored procedure that can
help? Dropping a replication subscription and publication removes these
columns so I suspect that the information is available somewhere.
Thanks
Dropping a publication doesn't drop the guid columns added to a merge
publication. As far as I know we can't identify those columns added.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||What about something like this:
declare @.mystring varchar(2000)
set @.mystring='select '
select @.mystring=@.mystring+' '+name+', ' From syscolumns where
id=object_id('customers')
and name <>'rowguid'
select @.mystring=substring(@.mystring,1,len(@.mystring)-1)+' from customers'
print @.mystring
Hilary Cotter
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
"Kohn" <Kohn@.discussions.microsoft.com> wrote in message
news:915755D4-E2FC-4790-B0E8-6E493DE1BA4F@.microsoft.com...
>I need to dynamically generate a select command that does not include
> replication generated columns. Is there a way to do this? I could use
> GetOleDbSchemaTable and filter for column names with "rowguid" but that
> does
> not seem robust to me. Is there a system table or stored procedure that
> can
> help? Dropping a replication subscription and publication removes these
> columns so I suspect that the information is available somewhere.
> Thanks
>
|||Hi Hilary - unfortunately this doesn't work . We could query for the
rowguid column on the table, but that might already have existed prior to
the replication setup. As far as I can tell there isn't a way of knowing if
the rowguid column is added by the replication setup or by a user
beforehand.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||It will work for the majority of the cases.
Hilary Cotter
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O$uP0uJNHHA.3424@.TK2MSFTNGP02.phx.gbl...
> Hi Hilary - unfortunately this doesn't work . We could query for the
> rowguid column on the table, but that might already have existed prior to
> the replication setup. As far as I can tell there isn't a way of knowing
> if the rowguid column is added by the replication setup or by a user
> beforehand.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||Another thing to take into account is that replication does not require
the column to be called rowguid. It just requires a column with the
ROWGUIDCOL property set. Maybe that is where you should start looking.
Hilary Cotter wrote:
> What about something like this:
> declare @.mystring varchar(2000)
> set @.mystring='select '
> select @.mystring=@.mystring+' '+name+', ' From syscolumns where
> id=object_id('customers')
> and name <>'rowguid'
> select @.mystring=substring(@.mystring,1,len(@.mystring)-1)+' from customers'
> print @.mystring
>
|||I had a quick look and it seems you have to look for colomns where
syscolumns.colstat = 2. You can also only have one column per table with
the ROWGUIDCOL property set, so you can be pretty sure that is the
column used by replication. So to modify Hilary's query:
declare @.mystring varchar(2000)
set @.mystring='select '
select @.mystring=@.mystring+' '+name+', ' From syscolumns where
id=object_id('customers')
and colstat <> 2
select @.mystring=substring(@.mystring,1,len(@.mystring)-1)+' from
customers'
print @.mystring
JE wrote:[vbcol=seagreen]
> Another thing to take into account is that replication does not require
> the column to be called rowguid. It just requires a column with the
> ROWGUIDCOL property set. Maybe that is where you should start looking.
>
> Hilary Cotter wrote:
|||The information for identifying the column with the rowguid property solves
the problem. My app retrieves the information with the GetOleDbSchemaTable
function (see below).
By the way, rowguid columns created by the wizard are removed when dropping
the publication. I suspect it uses the preserve_rowguidcol column in the
sysmergearticles tables.
cn.Open()
Dim t As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
New Object() {Nothing, Nothing, TableName, Nothing})
cn.Close()
cn.Dispose()
Dim SelectRows() As DataRow
SelectRows = t.Select("(DATA_TYPE <> 72) AND
(COLUMN_HASDEFAULT=FALSE) AND (COLUMN_HASDEFAULT=False) AND
((ISNULL(COLUMN_DEFAULT,'T')='T') OR
(COLUMN_DEFAULT<>'(newsequentialid())'))")
Dim SelectListStringBuilder As New System.Text.StringBuilder
For Each r As DataRow In SelectRows
SelectListStringBuilder.Append(r.Item("COLUMN_NAME "))
SelectListStringBuilder.Append(",")
Next
SelectListStringBuilder.Length -= 1
Debug.WriteLine(SelectListStringBuilder.ToString)
Thanks for the help

How do I generate auto increment number in SQL Express?

Hi, in Access, I can use an Auto-Increment number for my primary key field. May I know how do I do that in SQL Express? In addition, is there any tutorial on how to use SQL Express to generate customised unique numbers (such as membership number, Customer ID such as A001 where A is based on the customer's name while 001 is due to the fact that the customer is the first among those with names starting with A)?Thanks a lot.For your first question: In SQL Server, you can use data type int as your identity column and assign this column as indentity field from the property window. It will work like autonumber field in Access.|||

cckiat:

Hi, in Access, I can use an Auto-Increment number for my primary key field. May I know how do I do that in SQL Express? In addition, is there any tutorial on how to use SQL Express to generate customised unique numbers (such as membership number, Customer ID such as A001 where A is based on the customer's name while 001 is due to the fact that the customer is the first among those with names starting with A)? Thanks a lot.

IDENTITY is the auto increament in SQL Server it is a property of the column, the second one you described is a SEQUENCE it is in Oracle not SQL Server but it is similar to IDENTITY. Both are defined by ANSI SQL but Microsoft and Oracle choose to implement one and not the other. But you can use GUID in SQL Server to generate Unique numbers but it is a 16bytes Binary data type so use it with care. Hope this helps.

How do I generate a Unique ID?

Hi,

I have 5 tables that need ID columns. These ID columns are the primary keys for these tables. They appear in the tables like so:

Clients table

CLT00001
CLT00002
CLT00003

Volunteers table

VOL00001
VOL00002
etc...

These ID's are given to the Clients/Volunteers so they need to be more than just a number (I want to use the 3 char prefix), but I want them to auto-increment. I am not concerned with gaps if a row is deleted.

At the moment I have a user defined datatype in MSSQL, and I am incrementing and adding the prefix in VB before inserting into the database.

This works fine, except I dont know how to retrieve the last ID number from the DB so I can increment it when I add a new record.

I was using a VB function that did this: SELECT MAX(client_id) FROM clients, then strip everything of the front but the number, increment + 1, add the prefix and leading zeros back on again, and return the new ID. This worked until CLT00011, then it returned CLT00002 again.

Is there a better way to do what I am trying to do? Please help!One solution:

Separate your ID into two fields; the prefix and the numeric portions. Autoincrement the numeric portion, but pad it with zeros and concatenate it with the prefix for display. You could also create a calculated field in your table that combined the two components.

Either way, you can set the combination of the two columns as the primary key, or just as a unique index.

blindman|||Does that mean I would have a Prefix field with 'VOL' in every row, and my IDENTITY field in another. Sounds good, but does it kmatter that I have redundant info. i.e. the prefix field?

How do I join the two with a calculated field, because I still need to have a 8 char long ID, 3 chars for the prefix, then the ID num padded with zeros between the two, e.g.

VOL00001
VOL00002|||Create a trigger what will generate primary key for your table (you can use autoincremet field or calculate new key).|||If every record has the same prefix then is it not necessary to dedicate a column to it.

Set up your autoincrement row, and then create your calculated field with this formula:

'VOL' + right('00000' + cast([AutoIncColumn] as varchar(5)))

blindman

How DO I Generate a Table Of Contents For a Report?

Hi!!!

Can anybody help me with the generation of Table of Contents for a report using SQL Server 2005 Reporting Services. Let me ellaborate. The scenerio is...i m having a report of lets say 500 pages grouped on employees, showing the performance of each employee between specific date range. Now if the manager prints the report of 500 pages he will be more intersted to jump directly to a perticular employee's page which means my printed report had to have a Table of Contents on my grouped criteria (which in this case is employee number). I would really appriciate if someone can suggest me a solution for this.

Any reason why all 500 pages have to be printed? If your manager can agree to viewing the information on line, your best option would be to use a document map. Not sure if you can print the information used to build the document map but I'm guessing your table of contents would at least begin with a document map. BTW, document map properties are an attribute of a group.|||Yes actually i had that option of Document Map in mind but the thing is, there are scenerios where lenghty reports are printed and taken to meetings with every person having its own copy of report and they have to discuss those reports along with directly taking notes/comments on the printed report for future reference. So its a sort of compulsion to have TOC with page number reference for printed reports so that they can quickly jump to any specific page for discussion. I appriciate your reply but do you have any ideas to get it done by any means may be through coding or any other way?|||

Anybody? Its really urgent.

Thanx.

|||There should be some simple work around for this thing if it does not have a built-in support for it? any ideas guys?|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select. If you have any questions i can explain them in more details

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Thanx!

How DO I Generate a Table Of Contents For a Report?

Hi!!!

Can anybody help me with the generation of Table of Contents for a report using SQL Server 2005 Reporting Services. Let me ellaborate. The scenerio is...i m having a report of lets say 500 pages grouped on employees, showing the performance of each employee between specific date range. Now if the manager prints the report of 500 pages he will be more intersted to jump directly to a perticular employee's page which means my printed report had to have a Table of Contents on my grouped criteria (which in this case is employee number). I would really appriciate if someone can suggest me a solution for this.

Any reason why all 500 pages have to be printed? If your manager can agree to viewing the information on line, your best option would be to use a document map. Not sure if you can print the information used to build the document map but I'm guessing your table of contents would at least begin with a document map. BTW, document map properties are an attribute of a group.|||Yes actually i had that option of Document Map in mind but the thing is, there are scenerios where lenghty reports are printed and taken to meetings with every person having its own copy of report and they have to discuss those reports along with directly taking notes/comments on the printed report for future reference. So its a sort of compulsion to have TOC with page number reference for printed reports so that they can quickly jump to any specific page for discussion. I appriciate your reply but do you have any ideas to get it done by any means may be through coding or any other way?|||

Anybody? Its really urgent.

Thanx.

|||There should be some simple work around for this thing if it does not have a built-in support for it? any ideas guys?|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select. If you have any questions i can explain them in more details

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Thanx!

|||I started down this path, and found quite quickly that I cannot reference Globals.PageNumber() within the body...therefore, I cannot tell a function that "Strategy A is on page 5", etc... How did you solve this problem?

Thx,
Mojo

|||

I too would like to know if there is an easy way to add Table of Content.

The work around I use is, Right click on any groups / details area --> Nevigation tab --> Document map..Using drop down, choose the data element file that you wish to include in Table of Content.

The document map builds a nevigation menu on the left side pane of the report window.

|||

Its been a while since i did that, but lets try this,

Write your custom assembly with following code ..

using System;

using System.Collections.Generic;

using System.Text;

namespace ReportsCustomAssembly

{

public class CustomCode

{

public static int CurrPageNum;

public static int SavePage(int CurrPage )

{

CurrPageNum=CurrPage;

return CurrPageNum;

}

}

}

Build your assembly and add reference it in the report. Now in the page header add a text box and wirte expression =ReportsCustomAssembly.CustomCode.SavePage(Globals!PageNumber)

Now you have your page number stored in the static varible "CurrPageNum" defined in your custom assembly. You can reuse it in body or anywhere else in the report.

=ReportsCustomAssembly.CustomCode.CurrPageNum

I hope it should work, i remember vaugely this is somewhat i did, but lemme know if you face any problems i can go back and recheck how exactly i did that.

|||

I am afraid my friend, I haven't been able to find any solution at all which is straight forward. I expected it to be a straight forward thing too but didnt work that way.

I would highly recommend if the document map can be used as an alternative to the TOC in your case, you should use it, because it is much easier to use, provides you with interactive hyper links on the report and most of all if you export it to PDF or Excel it creates the the bookmarks equvilant to the document map you have on the report.

However in my specific scenerio i needed a TOC because my business requirement was to print out big reports starting with TOC right at the top.

How DO I Generate a Table Of Contents For a Report?

Hi!!!

Can anybody help me with the generation of Table of Contents for a report using SQL Server 2005 Reporting Services. Let me ellaborate. The scenerio is...i m having a report of lets say 500 pages grouped on employees, showing the performance of each employee between specific date range. Now if the manager prints the report of 500 pages he will be more intersted to jump directly to a perticular employee's page which means my printed report had to have a Table of Contents on my grouped criteria (which in this case is employee number). I would really appriciate if someone can suggest me a solution for this.

Any reason why all 500 pages have to be printed? If your manager can agree to viewing the information on line, your best option would be to use a document map. Not sure if you can print the information used to build the document map but I'm guessing your table of contents would at least begin with a document map. BTW, document map properties are an attribute of a group.|||Yes actually i had that option of Document Map in mind but the thing is, there are scenerios where lenghty reports are printed and taken to meetings with every person having its own copy of report and they have to discuss those reports along with directly taking notes/comments on the printed report for future reference. So its a sort of compulsion to have TOC with page number reference for printed reports so that they can quickly jump to any specific page for discussion. I appriciate your reply but do you have any ideas to get it done by any means may be through coding or any other way?|||

Anybody? Its really urgent.

Thanx.

|||There should be some simple work around for this thing if it does not have a built-in support for it? any ideas guys?|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select. If you have any questions i can explain them in more details

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Thanx!