Changing column values on table insert trigger

Hello,

i have a table and if a record is inserted i will test a numeric value
in this table. If the this value is greather as 1 million, than an
status column should be changed from 'A' to 'B'.

Yes and sorry, this is a newbie question.

On Oracle this work simple:

create trigger myTrigger on tableX
as
begin
if :old.x 100000 then
:new.y:='B'
end if;
end;

Thanks
MaikYou can look up SQL Server Books Online to learn how to write a trigger in
t-SQL. You'll need to utilize the inserted/deleted virtual tables to
accomodate multi-row inserts. The update statement will be something along
the lines of:

UPDATE tableX
SET y = B
WHERE EXISTS ( SELECT *
FROM inserted i
WHERE i.key_col = tableX.key_col
AND inserted.x 1000000 ) ;

Here key_col is any column or set of columns that can uniquely identify a
row in tableX.

--
Anith|||Hi,

Quote:

Originally Posted by

i have a table and if a record is inserted i will test a numeric value
in this table. If the this value is greather as 1 million, than an
status column should be changed from 'A' to 'B'.
>
Yes and sorry, this is a newbie question.
>
On Oracle this work simple:
>
create trigger myTrigger on tableX
as
begin
if :old.x 100000 then
:new.y:='B'
end if;
end;

MS SQL Server doesn't have "before action" triggers, so you
have to update the table and use the INSERTED virtual table
to know what records were inserted into the table.

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.comsql

Changing color of the details depending on the value of the field

Hello !
I am using Reporting Services with SQL Server 2000 (SP3).
I am trying to build in dynamic coloring into my report depending on the
value in a certain field. Query already returns name of the color I would
like to use as an additional field (color conditions are driven by range of
data certain value falls into and stored in the database ). I simple need to
pick color stored in this field( it is Dataset field Color) and apply to
particular cell (possibly entire row) when rendering report. Colors are
returned as names: GREEN,RED,GREY etc.
Is there an easy way to accomplish this in Reporting Services?
Any help would be greatly appreciated.
IgorSelect the cell you want to colorize according to the value. Change the
BackColor property and use a VB expression instead of a hard-coded
color. Use the equal sign "=" in order to use a VB expression, just
like this :
=Fields!Color.Value
Since Green, Red and Gray are know colors, they will be authorized.
You also can do something like this (if my sales rate is under 50%, I
colorize in Red, otherwise, in green) :
=IIF(Fields!SalesRate.Value < 50, "Red", "Green")|||Thanks a lot Julich! It worked perfectly! I would actually like to stored
color logic inside that database so that query returns color instead of
having RS making this decisions.
Thanks again,
Igor
"Julich" <julich@.gmail.com> wrote in message
news:1118320972.223994.206500@.z14g2000cwz.googlegroups.com...
> Select the cell you want to colorize according to the value. Change the
> BackColor property and use a VB expression instead of a hard-coded
> color. Use the equal sign "=" in order to use a VB expression, just
> like this :
> =Fields!Color.Value
> Since Green, Red and Gray are know colors, they will be authorized.
> You also can do something like this (if my sales rate is under 50%, I
> colorize in Red, otherwise, in green) :
> =IIF(Fields!SalesRate.Value < 50, "Red", "Green")
>

Changing a null to another value in select?

Hi all,
I have a result set coming back with one item that is null, because I am
trying to sort by this column this result is always at the top of the result
set, however I need them to be at the bottom of the result set, I cant
change the ordering because then the other results will no longer be
alphabetically sorted. Is there a way to change a 'null' to something else
if it is returned in a result set? My hope was perhaps to set it to be 10
Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
happens) be at the bottom?
Possible?
Regards
RobSelect isnull(mycolumn, 'zzzzzzzzzz') from MyTable
Hope this helps
Chris
"Rob Meade" <robb.meade@.NO-SPAM.kingswoodweb.net> wrote in message
news:4BbJd.17892$GG1.5654@.text.news.blueyonder.co.uk...
> Hi all,
> I have a result set coming back with one item that is null, because I am
> trying to sort by this column this result is always at the top of the
> result
> set, however I need them to be at the bottom of the result set, I cant
> change the ordering because then the other results will no longer be
> alphabetically sorted. Is there a way to change a 'null' to something
> else
> if it is returned in a result set? My hope was perhaps to set it to be 10
> Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
> happens) be at the bottom?
> Possible?
> Regards
> Rob
>|||"Chris, Master of All Things Insignificant" wrote ...

> Select isnull(mycolumn, 'zzzzzzzzzz') from MyTable
Hi Chris,
Thanks for the reply. I managed something similar myself, here's my view:
SELECT '1' AS ViewOrder1, '1' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, c.SectionName, c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE b.SectionID IS NOT NULL AND b.SectionID <> '0' AND a.NGID <> '0'
AND b.NGIsNoGroup = '0'
UNION ALL
SELECT '1' AS ViewOrder1, '2' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, c.SectionName, c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE b.SectionID IS NOT NULL AND b.SectionID <> '0' AND a.NGID <> '0'
AND b.NGIsNoGroup = '1'
UNION ALL
SELECT '3' AS ViewOrder1, '1' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, 'ZZZZZZZZZZ', c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE (b.SectionID IS NULL OR
b.SectionID = '0') AND a.NGID <> '0'
UNION ALL
SELECT '4' AS ViewOrder1, '1' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, 'ZZZZZZZZZZ', c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE (b.SectionID IS NULL OR
b.SectionID = '0') AND a.NGID = '0'
As you can see the last 2 sections of this (where viewOrder1 is either 3 or
4 etc) I have inserted the 'ZZZZZZZZZZ' as the section name...
This does work, but feels a bit flakey...if someone was to actually create a
'section' in my application called 'ZZZZZZZZZZZ' it would obviously cause
problems...
I originally had in my ASP code 4 statements which went off and got the
respective results for each case, but I thought making just one hit would be
better, now I face a different problem...
Any ideas anyone?
Regards
Rob|||Use a CASE expression in the ORDER BY clause.
Example:
use northwind
go
select
customerid,
companyname,
region
from
customers
order by
case when region is null then 1 else 0 end asc,
customerid
go
AMB
"Rob Meade" wrote:

> Hi all,
> I have a result set coming back with one item that is null, because I am
> trying to sort by this column this result is always at the top of the resu
lt
> set, however I need them to be at the bottom of the result set, I cant
> change the ordering because then the other results will no longer be
> alphabetically sorted. Is there a way to change a 'null' to something els
e
> if it is returned in a result set? My hope was perhaps to set it to be 10
> Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
> happens) be at the bottom?
> Possible?
> Regards
> Rob
>
>|||"Alejandro Mesa" wrote ...

> Use a CASE expression in the ORDER BY clause.

> use northwind
(wishing now I'd not deleted that recently)

> order by
> case when region is null then 1 else 0 end asc,
> customerid
That looks like what I want, I think, I cant quite work out the 1 / 0 thing
though - whats that actually going to do - in words etc...
ie, does that make region = 1 if it = null, and = 0 if its not null?
Thanks for the reply.
Regards
Rob|||"Alejandro Mesa" wrote ...

> Use a CASE expression in the ORDER BY clause.
>
[snip]
Hi Alejandro,
I was unable to get it to work in the ORDER BY clause, but your reply did
make me go and investigate the use of CASE expressions, I've now popped it
in the SELECT statement and its working perfectly.
Thank you.
Regards
Rob|||Rob,
ORDER BY
CASE WHEN itemColumn IS NULL THEN 1 ELSE 0 END,
itemColumn
Steve Kass
Drew University
Rob Meade wrote:

>Hi all,
>I have a result set coming back with one item that is null, because I am
>trying to sort by this column this result is always at the top of the resul
t
>set, however I need them to be at the bottom of the result set, I cant
>change the ordering because then the other results will no longer be
>alphabetically sorted. Is there a way to change a 'null' to something else
>if it is returned in a result set? My hope was perhaps to set it to be 10
>Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
>happens) be at the bottom?
>Possible?
>Regards
>Rob
>
>|||"Steve Kass" wrote ...

> ORDER BY
> CASE WHEN itemColumn IS NULL THEN 1 ELSE 0 END,
> itemColumn
Hi Steve,
Could you explain to me what the 1 and 0 are/do please?
Regards
Rob|||Imagine that you put this in the SELECT list instead:
SELECT ... CASE WHEN itemColumn IS NULL THEN 1 ELSE 0 END AS sortcol
For a row with NULL in itemColumn, the expression will return the value 1. I
f there is a non-null
value in itemColumn, the expression will return the value 0. So, say you hav
e some rows (sortcol is
the one produced by above expression)
ItemColumn SortCol
23 0
NULL 1
1 0
NULL 1
Now, imagine that you do:
ORDER BY SortCol, ItemColumn
Get it?
The "short cut" Steve did is to put the expression in the ORDER BY clause in
stead of in the SELECT
clause. Logically, the columns didn't "materialize" before the sort operatio
n...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Rob Meade" <robb.meade@.NO-SPAM.kingswoodweb.net> wrote in message
news:8roJd.18310$GG1.8683@.text.news.blueyonder.co.uk...
> "Steve Kass" wrote ...
>
> Hi Steve,
> Could you explain to me what the 1 and 0 are/do please?
> Regards
> Rob
>|||"Tibor Karaszi" wrote ...

> ItemColumn SortCol
> 23 0
> NULL 1
> 1 0
> NULL 1
> Now, imagine that you do:
> ORDER BY SortCol, ItemColumn
> Get it?
> The "short cut" Steve did is to put the expression in the ORDER BY clause
instead of in the SELECT
> clause. Logically, the columns didn't "materialize" before the sort
operation...
Hi Tibor,
Thanks for your reply...
I get it now, the 1 / 0 were for the ordering etc, as where I was using
1,2,3,4 previously...When I first looked at it I was wondering whether they
could only 'ever' be those values or something - its not something I've come
across/used before..
Thanks for the explanation.
Regards
Rob

changing a field''s value when user updates data

Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.

For changing the field value from ‘Yes’ no ‘No’ you can use the trigger (for update).

|||

Mani is correct. Here is an example of the update trigger.

e.g.

Code Snippet

create trigger _tr on InputData

for update

as

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

from InputData tb join Inserted i on tb.[pk] = i.[pk]

where tb.calculated='yes'

go

|||

Hi!

You can directly include "Calculated ='no'" statement within the your update command itself only, based on the condition only. otherwise all the records will get updated.

eg:

Update InsertData
Set
Column1 = value1,
Column2 = value2,
...,
Calculated ='no'
WHERE <Condition>

I hope it'll solve your problem and please let me know if I'm wrong.

Thanks & Regards,
Kiran.Y

|||I have some questions about using a trigger before I attempt to implement one...

1. Will the code snippet, using the Inserted table, work in SQL version 8?

2. Which database holds the table called Inserted? Is it the one I'm working in or the tempdb or one of the other system databases?

3. Do I store the trigger as a stored procedure?

4. My project consists of SQL tables with an MS Access front end. When the user updates the record via MS Access will the stored procedure containing the trigger automatically run? (This question assumes the trigger code will be stored as a stored procedure...if this is wrong, please do correct this assumption).

5. What does if @.@.rowcount = 0 return; do?

Thanks in advance.|||

1. Yes. Sql2k does support trigger.

2. Inserted (or deleted) table is a special memory-resident table that can only be accessed in a trigger.

3. Trigger is just another object in sqlserver, similar to stored procedure.

4. Yes. Trigger is an event based procedure. It's tied to an event of the table (insert/update/delete).

5. This line tells the system to go ahead and exit and skip the rest of the code within the trigger because there is no row affected by the event.

http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx

|||Your reply has helped to clarify things for me...thank-you.

I put the trigger in place on the InputData table and it works beautifully to set calculated = 'no' when the user makes an update to the data. However, when I attempt to assign 'yes' to the calculated field after the calculation stored procedures run, it will not. I am assuming this is because trying to set calculated = 'yes' constitutes an update to the table and per the trigger, any update to the row says to make calculated = 'no'. How do I get the trigger to fire when the update is made to any field in the table except for the calculated field?
|||

You can use IF UPDATE() clause inside the trigger to check.

e.g.

Code Snippet

IF UPDATE(calculated)

PRINT ('column [calculated] is modified')

|||As a follow up to this thread, here is how it worked out...

It seems I could not get this to work as I'd pictured using the calculated field (alas, I am a beginner so these things happen). I thought of a different approach to handle this problem. It works as follows:

I removed the calculated field from the InputData table, then created the following trigger:

Code Snippet

create trigger removeoldOutputData on InputData
for update
as
if @.@.rowcount=0 return;

delete from OutputData
where primarykey in (select primarykey from Inserted)

This trigger fires whenever a record is edited in the table InputData.

Then, the stored procedures that perform the math calculations specify which InputData records need to have calculations performed on them by comparing which primary keys in InputData do not yet have any records in OutputData. Here is a generic sample of the code:

Code Snippet

select
field1, field2, field3, ...., fieldn

into #inputdataforcalculations

from InputData
where primarykey not in (select primarykey from OutputData)

--more code follows to perform calculations on those records that were put into --#inputdataforcalculations

This where clause prevents the calculation stored procedures from recalculating existing data that has not been changed.

On the Access front end the calculation stored procedures are called in a pass-through query. There is a macro that performs an OpenQuery on the pass-through query, then this macro is automatically run when the user closes the form (event: On Close) that's used for entering/editing/deleting records in the underlying InputData SQL table.

I've run some tests on this approach and it appears to be functioning OK.

Thanks to all contributors above for my first lesson on triggers.

changing a field''s value when user updates data

Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.

For changing the field value from ‘Yes’ no ‘No’ you can use the trigger (for update).

|||

Mani is correct. Here is an example of the update trigger.

e.g.

Code Snippet

create trigger _tr on InputData

for update

as

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

from InputData tb join Inserted i on tb.[pk] = i.[pk]

where tb.calculated='yes'

go

|||

Hi!

You can directly include "Calculated ='no'" statement within the your update command itself only, based on the condition only. otherwise all the records will get updated.

eg:

Update InsertData
Set
Column1 = value1,
Column2 = value2,
...,
Calculated ='no'
WHERE <Condition>

I hope it'll solve your problem and please let me know if I'm wrong.

Thanks & Regards,
Kiran.Y

|||I have some questions about using a trigger before I attempt to implement one...

1. Will the code snippet, using the Inserted table, work in SQL version 8?

2. Which database holds the table called Inserted? Is it the one I'm working in or the tempdb or one of the other system databases?

3. Do I store the trigger as a stored procedure?

4. My project consists of SQL tables with an MS Access front end. When the user updates the record via MS Access will the stored procedure containing the trigger automatically run? (This question assumes the trigger code will be stored as a stored procedure...if this is wrong, please do correct this assumption).

5. What does if @.@.rowcount = 0 return; do?

Thanks in advance.|||

1. Yes. Sql2k does support trigger.

2. Inserted (or deleted) table is a special memory-resident table that can only be accessed in a trigger.

3. Trigger is just another object in sqlserver, similar to stored procedure.

4. Yes. Trigger is an event based procedure. It's tied to an event of the table (insert/update/delete).

5. This line tells the system to go ahead and exit and skip the rest of the code within the trigger because there is no row affected by the event.

http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx

|||Your reply has helped to clarify things for me...thank-you.

I put the trigger in place on the InputData table and it works beautifully to set calculated = 'no' when the user makes an update to the data. However, when I attempt to assign 'yes' to the calculated field after the calculation stored procedures run, it will not. I am assuming this is because trying to set calculated = 'yes' constitutes an update to the table and per the trigger, any update to the row says to make calculated = 'no'. How do I get the trigger to fire when the update is made to any field in the table except for the calculated field?
|||

You can use IF UPDATE() clause inside the trigger to check.

e.g.

Code Snippet

IF UPDATE(calculated)

PRINT ('column [calculated] is modified')

|||As a follow up to this thread, here is how it worked out...

It seems I could not get this to work as I'd pictured using the calculated field (alas, I am a beginner so these things happen). I thought of a different approach to handle this problem. It works as follows:

I removed the calculated field from the InputData table, then created the following trigger:

Code Snippet

create trigger removeoldOutputData on InputData
for update
as
if @.@.rowcount=0 return;

delete from OutputData
where primarykey in (select primarykey from Inserted)

This trigger fires whenever a record is edited in the table InputData.

Then, the stored procedures that perform the math calculations specify which InputData records need to have calculations performed on them by comparing which primary keys in InputData do not yet have any records in OutputData. Here is a generic sample of the code:

Code Snippet

select
field1, field2, field3, ...., fieldn

into #inputdataforcalculations

from InputData
where primarykey not in (select primarykey from OutputData)

--more code follows to perform calculations on those records that were put into --#inputdataforcalculations

This where clause prevents the calculation stored procedures from recalculating existing data that has not been changed.

On the Access front end the calculation stored procedures are called in a pass-through query. There is a macro that performs an OpenQuery on the pass-through query, then this macro is automatically run when the user closes the form (event: On Close) that's used for entering/editing/deleting records in the underlying InputData SQL table.

I've run some tests on this approach and it appears to be functioning OK.

Thanks to all contributors above for my first lesson on triggers.

changing a field''s value when user updates data

Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.

For changing the field value from ‘Yes’ no ‘No’ you can use the trigger (for update).

|||

Mani is correct. Here is an example of the update trigger.

e.g.

Code Snippet

create trigger _tr on InputData

for update

as

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

from InputData tb join Inserted i on tb.[pk] = i.[pk]

where tb.calculated='yes'

go

|||

Hi!

You can directly include "Calculated ='no'" statement within the your update command itself only, based on the condition only. otherwise all the records will get updated.

eg:

Update InsertData
Set
Column1 = value1,
Column2 = value2,
...,
Calculated ='no'
WHERE <Condition>

I hope it'll solve your problem and please let me know if I'm wrong.

Thanks & Regards,
Kiran.Y

|||I have some questions about using a trigger before I attempt to implement one...

1. Will the code snippet, using the Inserted table, work in SQL version 8?

2. Which database holds the table called Inserted? Is it the one I'm working in or the tempdb or one of the other system databases?

3. Do I store the trigger as a stored procedure?

4. My project consists of SQL tables with an MS Access front end. When the user updates the record via MS Access will the stored procedure containing the trigger automatically run? (This question assumes the trigger code will be stored as a stored procedure...if this is wrong, please do correct this assumption).

5. What does if @.@.rowcount = 0 return; do?

Thanks in advance.|||

1. Yes. Sql2k does support trigger.

2. Inserted (or deleted) table is a special memory-resident table that can only be accessed in a trigger.

3. Trigger is just another object in sqlserver, similar to stored procedure.

4. Yes. Trigger is an event based procedure. It's tied to an event of the table (insert/update/delete).

5. This line tells the system to go ahead and exit and skip the rest of the code within the trigger because there is no row affected by the event.

http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx

|||Your reply has helped to clarify things for me...thank-you.

I put the trigger in place on the InputData table and it works beautifully to set calculated = 'no' when the user makes an update to the data. However, when I attempt to assign 'yes' to the calculated field after the calculation stored procedures run, it will not. I am assuming this is because trying to set calculated = 'yes' constitutes an update to the table and per the trigger, any update to the row says to make calculated = 'no'. How do I get the trigger to fire when the update is made to any field in the table except for the calculated field?
|||

You can use IF UPDATE() clause inside the trigger to check.

e.g.

Code Snippet

IF UPDATE(calculated)

PRINT ('column [calculated] is modified')

|||As a follow up to this thread, here is how it worked out...

It seems I could not get this to work as I'd pictured using the calculated field (alas, I am a beginner so these things happen). I thought of a different approach to handle this problem. It works as follows:

I removed the calculated field from the InputData table, then created the following trigger:

Code Snippet

create trigger removeoldOutputData on InputData
for update
as
if @.@.rowcount=0 return;

delete from OutputData
where primarykey in (select primarykey from Inserted)

This trigger fires whenever a record is edited in the table InputData.

Then, the stored procedures that perform the math calculations specify which InputData records need to have calculations performed on them by comparing which primary keys in InputData do not yet have any records in OutputData. Here is a generic sample of the code:

Code Snippet

select
field1, field2, field3, ...., fieldn

into #inputdataforcalculations

from InputData
where primarykey not in (select primarykey from OutputData)

--more code follows to perform calculations on those records that were put into --#inputdataforcalculations

This where clause prevents the calculation stored procedures from recalculating existing data that has not been changed.

On the Access front end the calculation stored procedures are called in a pass-through query. There is a macro that performs an OpenQuery on the pass-through query, then this macro is automatically run when the user closes the form (event: On Close) that's used for entering/editing/deleting records in the underlying InputData SQL table.

I've run some tests on this approach and it appears to be functioning OK.

Thanks to all contributors above for my first lesson on triggers.

changing a field''s value when user updates data

Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.

For changing the field value from ‘Yes’ no ‘No’ you can use the trigger (for update).

|||

Mani is correct. Here is an example of the update trigger.

e.g.

Code Snippet

create trigger _tr on InputData

for update

as

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

from InputData tb join Inserted i on tb.[pk] = i.[pk]

where tb.calculated='yes'

go

|||

Hi!

You can directly include "Calculated ='no'" statement within the your update command itself only, based on the condition only. otherwise all the records will get updated.

eg:

Update InsertData
Set
Column1 = value1,
Column2 = value2,
...,
Calculated ='no'
WHERE <Condition>

I hope it'll solve your problem and please let me know if I'm wrong.

Thanks & Regards,
Kiran.Y

|||I have some questions about using a trigger before I attempt to implement one...

1. Will the code snippet, using the Inserted table, work in SQL version 8?

2. Which database holds the table called Inserted? Is it the one I'm working in or the tempdb or one of the other system databases?

3. Do I store the trigger as a stored procedure?

4. My project consists of SQL tables with an MS Access front end. When the user updates the record via MS Access will the stored procedure containing the trigger automatically run? (This question assumes the trigger code will be stored as a stored procedure...if this is wrong, please do correct this assumption).

5. What does if @.@.rowcount = 0 return; do?

Thanks in advance.|||

1. Yes. Sql2k does support trigger.

2. Inserted (or deleted) table is a special memory-resident table that can only be accessed in a trigger.

3. Trigger is just another object in sqlserver, similar to stored procedure.

4. Yes. Trigger is an event based procedure. It's tied to an event of the table (insert/update/delete).

5. This line tells the system to go ahead and exit and skip the rest of the code within the trigger because there is no row affected by the event.

http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx

|||Your reply has helped to clarify things for me...thank-you.

I put the trigger in place on the InputData table and it works beautifully to set calculated = 'no' when the user makes an update to the data. However, when I attempt to assign 'yes' to the calculated field after the calculation stored procedures run, it will not. I am assuming this is because trying to set calculated = 'yes' constitutes an update to the table and per the trigger, any update to the row says to make calculated = 'no'. How do I get the trigger to fire when the update is made to any field in the table except for the calculated field?
|||

You can use IF UPDATE() clause inside the trigger to check.

e.g.

Code Snippet

IF UPDATE(calculated)

PRINT ('column [calculated] is modified')

|||As a follow up to this thread, here is how it worked out...

It seems I could not get this to work as I'd pictured using the calculated field (alas, I am a beginner so these things happen). I thought of a different approach to handle this problem. It works as follows:

I removed the calculated field from the InputData table, then created the following trigger:

Code Snippet

create trigger removeoldOutputData on InputData
for update
as
if @.@.rowcount=0 return;

delete from OutputData
where primarykey in (select primarykey from Inserted)

This trigger fires whenever a record is edited in the table InputData.

Then, the stored procedures that perform the math calculations specify which InputData records need to have calculations performed on them by comparing which primary keys in InputData do not yet have any records in OutputData. Here is a generic sample of the code:

Code Snippet

select
field1, field2, field3, ...., fieldn

into #inputdataforcalculations

from InputData
where primarykey not in (select primarykey from OutputData)

--more code follows to perform calculations on those records that were put into --#inputdataforcalculations

This where clause prevents the calculation stored procedures from recalculating existing data that has not been changed.

On the Access front end the calculation stored procedures are called in a pass-through query. There is a macro that performs an OpenQuery on the pass-through query, then this macro is automatically run when the user closes the form (event: On Close) that's used for entering/editing/deleting records in the underlying InputData SQL table.

I've run some tests on this approach and it appears to be functioning OK.

Thanks to all contributors above for my first lesson on triggers.

sql

changing a field''s value when user updates data

Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.

For changing the field value from ‘Yes’ no ‘No’ you can use the trigger (for update).

|||

Mani is correct. Here is an example of the update trigger.

e.g.

Code Snippet

create trigger _tr on InputData

for update

as

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

from InputData tb join Inserted i on tb.[pk] = i.[pk]

where tb.calculated='yes'

go

|||

Hi!

You can directly include "Calculated ='no'" statement within the your update command itself only, based on the condition only. otherwise all the records will get updated.

eg:

Update InsertData
Set
Column1 = value1,
Column2 = value2,
...,
Calculated ='no'
WHERE <Condition>

I hope it'll solve your problem and please let me know if I'm wrong.

Thanks & Regards,
Kiran.Y

|||I have some questions about using a trigger before I attempt to implement one...

1. Will the code snippet, using the Inserted table, work in SQL version 8?

2. Which database holds the table called Inserted? Is it the one I'm working in or the tempdb or one of the other system databases?

3. Do I store the trigger as a stored procedure?

4. My project consists of SQL tables with an MS Access front end. When the user updates the record via MS Access will the stored procedure containing the trigger automatically run? (This question assumes the trigger code will be stored as a stored procedure...if this is wrong, please do correct this assumption).

5. What does if @.@.rowcount = 0 return; do?

Thanks in advance.|||

1. Yes. Sql2k does support trigger.

2. Inserted (or deleted) table is a special memory-resident table that can only be accessed in a trigger.

3. Trigger is just another object in sqlserver, similar to stored procedure.

4. Yes. Trigger is an event based procedure. It's tied to an event of the table (insert/update/delete).

5. This line tells the system to go ahead and exit and skip the rest of the code within the trigger because there is no row affected by the event.

http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx

|||Your reply has helped to clarify things for me...thank-you.

I put the trigger in place on the InputData table and it works beautifully to set calculated = 'no' when the user makes an update to the data. However, when I attempt to assign 'yes' to the calculated field after the calculation stored procedures run, it will not. I am assuming this is because trying to set calculated = 'yes' constitutes an update to the table and per the trigger, any update to the row says to make calculated = 'no'. How do I get the trigger to fire when the update is made to any field in the table except for the calculated field?
|||

You can use IF UPDATE() clause inside the trigger to check.

e.g.

Code Snippet

IF UPDATE(calculated)

PRINT ('column [calculated] is modified')

|||As a follow up to this thread, here is how it worked out...

It seems I could not get this to work as I'd pictured using the calculated field (alas, I am a beginner so these things happen). I thought of a different approach to handle this problem. It works as follows:

I removed the calculated field from the InputData table, then created the following trigger:

Code Snippet

create trigger removeoldOutputData on InputData
for update
as
if @.@.rowcount=0 return;

delete from OutputData
where primarykey in (select primarykey from Inserted)

This trigger fires whenever a record is edited in the table InputData.

Then, the stored procedures that perform the math calculations specify which InputData records need to have calculations performed on them by comparing which primary keys in InputData do not yet have any records in OutputData. Here is a generic sample of the code:

Code Snippet

select
field1, field2, field3, ...., fieldn

into #inputdataforcalculations

from InputData
where primarykey not in (select primarykey from OutputData)

--more code follows to perform calculations on those records that were put into --#inputdataforcalculations

This where clause prevents the calculation stored procedures from recalculating existing data that has not been changed.

On the Access front end the calculation stored procedures are called in a pass-through query. There is a macro that performs an OpenQuery on the pass-through query, then this macro is automatically run when the user closes the form (event: On Close) that's used for entering/editing/deleting records in the underlying InputData SQL table.

I've run some tests on this approach and it appears to be functioning OK.

Thanks to all contributors above for my first lesson on triggers.

changing a field''s value when user updates data

Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.

For changing the field value from ‘Yes’ no ‘No’ you can use the trigger (for update).

|||

Mani is correct. Here is an example of the update trigger.

e.g.

Code Snippet

create trigger _tr on InputData

for update

as

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

from InputData tb join Inserted i on tb.[pk] = i.[pk]

where tb.calculated='yes'

go

|||

Hi!

You can directly include "Calculated ='no'" statement within the your update command itself only, based on the condition only. otherwise all the records will get updated.

eg:

Update InsertData
Set
Column1 = value1,
Column2 = value2,
...,
Calculated ='no'
WHERE <Condition>

I hope it'll solve your problem and please let me know if I'm wrong.

Thanks & Regards,
Kiran.Y

|||I have some questions about using a trigger before I attempt to implement one...

1. Will the code snippet, using the Inserted table, work in SQL version 8?

2. Which database holds the table called Inserted? Is it the one I'm working in or the tempdb or one of the other system databases?

3. Do I store the trigger as a stored procedure?

4. My project consists of SQL tables with an MS Access front end. When the user updates the record via MS Access will the stored procedure containing the trigger automatically run? (This question assumes the trigger code will be stored as a stored procedure...if this is wrong, please do correct this assumption).

5. What does if @.@.rowcount = 0 return; do?

Thanks in advance.|||

1. Yes. Sql2k does support trigger.

2. Inserted (or deleted) table is a special memory-resident table that can only be accessed in a trigger.

3. Trigger is just another object in sqlserver, similar to stored procedure.

4. Yes. Trigger is an event based procedure. It's tied to an event of the table (insert/update/delete).

5. This line tells the system to go ahead and exit and skip the rest of the code within the trigger because there is no row affected by the event.

http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx

|||Your reply has helped to clarify things for me...thank-you.

I put the trigger in place on the InputData table and it works beautifully to set calculated = 'no' when the user makes an update to the data. However, when I attempt to assign 'yes' to the calculated field after the calculation stored procedures run, it will not. I am assuming this is because trying to set calculated = 'yes' constitutes an update to the table and per the trigger, any update to the row says to make calculated = 'no'. How do I get the trigger to fire when the update is made to any field in the table except for the calculated field?
|||

You can use IF UPDATE() clause inside the trigger to check.

e.g.

Code Snippet

IF UPDATE(calculated)

PRINT ('column [calculated] is modified')

|||As a follow up to this thread, here is how it worked out...

It seems I could not get this to work as I'd pictured using the calculated field (alas, I am a beginner so these things happen). I thought of a different approach to handle this problem. It works as follows:

I removed the calculated field from the InputData table, then created the following trigger:

Code Snippet

create trigger removeoldOutputData on InputData
for update
as
if @.@.rowcount=0 return;

delete from OutputData
where primarykey in (select primarykey from Inserted)

This trigger fires whenever a record is edited in the table InputData.

Then, the stored procedures that perform the math calculations specify which InputData records need to have calculations performed on them by comparing which primary keys in InputData do not yet have any records in OutputData. Here is a generic sample of the code:

Code Snippet

select
field1, field2, field3, ...., fieldn

into #inputdataforcalculations

from InputData
where primarykey not in (select primarykey from OutputData)

--more code follows to perform calculations on those records that were put into --#inputdataforcalculations

This where clause prevents the calculation stored procedures from recalculating existing data that has not been changed.

On the Access front end the calculation stored procedures are called in a pass-through query. There is a macro that performs an OpenQuery on the pass-through query, then this macro is automatically run when the user closes the form (event: On Close) that's used for entering/editing/deleting records in the underlying InputData SQL table.

I've run some tests on this approach and it appears to be functioning OK.

Thanks to all contributors above for my first lesson on triggers.

Changing a fields backgroung color IIF

Hi All,
I need to change the backgroung color to a field in the background property...

=iif(Fields!act_shp_dt.Value <> Fields!prm_shp_dt.Value , " Late ","")
This is the field and I want it to change color if the statement is true. Can anyone help?
Thank you in advanced, Kerrie

I never attempted this on a per cell basis but it should work;
Create a function in the code section, something like this...
function getColor(s1 as string, s2 as string) as String
getColor = iif((s1 <> s2), "Red", "White")
end function
then call the function from your textbox like this...
<BackgroundColor>=Code.getColor(Fields!act_shp_dt.Value , Fields!prm_shp_dt.Value )</BackgroundColor>
|||you code seems fine but make sure you use the colors you want to show:
=iif(Fields!act_shp_dt.Value <> Fields!prm_shp_dt.Value , " <some_color>","<some_color>")

changing a field's value when user updates data

Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.

For changing the field value from ‘Yes’ no ‘No’ you can use the trigger (for update).

|||

Mani is correct. Here is an example of the update trigger.

e.g.

Code Snippet

create trigger _tr on InputData

for update

as

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

from InputData tb join Inserted i on tb.[pk] = i.[pk]

where tb.calculated='yes'

go

|||

Hi!

You can directly include "Calculated ='no'" statement within the your update command itself only, based on the condition only. otherwise all the records will get updated.

eg:

Update InsertData
Set
Column1 = value1,
Column2 = value2,
...,
Calculated ='no'
WHERE <Condition>

I hope it'll solve your problem and please let me know if I'm wrong.

Thanks & Regards,
Kiran.Y

|||I have some questions about using a trigger before I attempt to implement one...

1. Will the code snippet, using the Inserted table, work in SQL version 8?

2. Which database holds the table called Inserted? Is it the one I'm working in or the tempdb or one of the other system databases?

3. Do I store the trigger as a stored procedure?

4. My project consists of SQL tables with an MS Access front end. When the user updates the record via MS Access will the stored procedure containing the trigger automatically run? (This question assumes the trigger code will be stored as a stored procedure...if this is wrong, please do correct this assumption).

5. What does if @.@.rowcount = 0 return; do?

Thanks in advance.|||

1. Yes. Sql2k does support trigger.

2. Inserted (or deleted) table is a special memory-resident table that can only be accessed in a trigger.

3. Trigger is just another object in sqlserver, similar to stored procedure.

4. Yes. Trigger is an event based procedure. It's tied to an event of the table (insert/update/delete).

5. This line tells the system to go ahead and exit and skip the rest of the code within the trigger because there is no row affected by the event.

http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx

|||Your reply has helped to clarify things for me...thank-you.

I put the trigger in place on the InputData table and it works beautifully to set calculated = 'no' when the user makes an update to the data. However, when I attempt to assign 'yes' to the calculated field after the calculation stored procedures run, it will not. I am assuming this is because trying to set calculated = 'yes' constitutes an update to the table and per the trigger, any update to the row says to make calculated = 'no'. How do I get the trigger to fire when the update is made to any field in the table except for the calculated field?
|||

You can use IF UPDATE() clause inside the trigger to check.

e.g.

Code Snippet

IF UPDATE(calculated)

PRINT ('column [calculated] is modified')

|||As a follow up to this thread, here is how it worked out...

It seems I could not get this to work as I'd pictured using the calculated field (alas, I am a beginner so these things happen). I thought of a different approach to handle this problem. It works as follows:

I removed the calculated field from the InputData table, then created the following trigger:

Code Snippet

create trigger removeoldOutputData on InputData
for update
as
if @.@.rowcount=0 return;

delete from OutputData
where primarykey in (select primarykey from Inserted)

This trigger fires whenever a record is edited in the table InputData.

Then, the stored procedures that perform the math calculations specify which InputData records need to have calculations performed on them by comparing which primary keys in InputData do not yet have any records in OutputData. Here is a generic sample of the code:

Code Snippet

select
field1, field2, field3, ...., fieldn

into #inputdataforcalculations

from InputData
where primarykey not in (select primarykey from OutputData)

--more code follows to perform calculations on those records that were put into --#inputdataforcalculations

This where clause prevents the calculation stored procedures from recalculating existing data that has not been changed.

On the Access front end the calculation stored procedures are called in a pass-through query. There is a macro that performs an OpenQuery on the pass-through query, then this macro is automatically run when the user closes the form (event: On Close) that's used for entering/editing/deleting records in the underlying InputData SQL table.

I've run some tests on this approach and it appears to be functioning OK.

Thanks to all contributors above for my first lesson on triggers.

changing a field's value when user updates data

Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.

For changing the field value from ‘Yes’ no ‘No’ you can use the trigger (for update).

|||

Mani is correct. Here is an example of the update trigger.

e.g.

Code Snippet

create trigger _tr on InputData

for update

as

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

from InputData tb join Inserted i on tb.[pk] = i.[pk]

where tb.calculated='yes'

go

|||

Hi!

You can directly include "Calculated ='no'" statement within the your update command itself only, based on the condition only. otherwise all the records will get updated.

eg:

Update InsertData
Set
Column1 = value1,
Column2 = value2,
...,
Calculated ='no'
WHERE <Condition>

I hope it'll solve your problem and please let me know if I'm wrong.

Thanks & Regards,
Kiran.Y

|||I have some questions about using a trigger before I attempt to implement one...

1. Will the code snippet, using the Inserted table, work in SQL version 8?

2. Which database holds the table called Inserted? Is it the one I'm working in or the tempdb or one of the other system databases?

3. Do I store the trigger as a stored procedure?

4. My project consists of SQL tables with an MS Access front end. When the user updates the record via MS Access will the stored procedure containing the trigger automatically run? (This question assumes the trigger code will be stored as a stored procedure...if this is wrong, please do correct this assumption).

5. What does if @.@.rowcount = 0 return; do?

Thanks in advance.|||

1. Yes. Sql2k does support trigger.

2. Inserted (or deleted) table is a special memory-resident table that can only be accessed in a trigger.

3. Trigger is just another object in sqlserver, similar to stored procedure.

4. Yes. Trigger is an event based procedure. It's tied to an event of the table (insert/update/delete).

5. This line tells the system to go ahead and exit and skip the rest of the code within the trigger because there is no row affected by the event.

http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx

|||Your reply has helped to clarify things for me...thank-you.

I put the trigger in place on the InputData table and it works beautifully to set calculated = 'no' when the user makes an update to the data. However, when I attempt to assign 'yes' to the calculated field after the calculation stored procedures run, it will not. I am assuming this is because trying to set calculated = 'yes' constitutes an update to the table and per the trigger, any update to the row says to make calculated = 'no'. How do I get the trigger to fire when the update is made to any field in the table except for the calculated field?
|||

You can use IF UPDATE() clause inside the trigger to check.

e.g.

Code Snippet

IF UPDATE(calculated)

PRINT ('column [calculated] is modified')

|||As a follow up to this thread, here is how it worked out...

It seems I could not get this to work as I'd pictured using the calculated field (alas, I am a beginner so these things happen). I thought of a different approach to handle this problem. It works as follows:

I removed the calculated field from the InputData table, then created the following trigger:

Code Snippet

create trigger removeoldOutputData on InputData
for update
as
if @.@.rowcount=0 return;

delete from OutputData
where primarykey in (select primarykey from Inserted)

This trigger fires whenever a record is edited in the table InputData.

Then, the stored procedures that perform the math calculations specify which InputData records need to have calculations performed on them by comparing which primary keys in InputData do not yet have any records in OutputData. Here is a generic sample of the code:

Code Snippet

select
field1, field2, field3, ...., fieldn

into #inputdataforcalculations

from InputData
where primarykey not in (select primarykey from OutputData)

--more code follows to perform calculations on those records that were put into --#inputdataforcalculations

This where clause prevents the calculation stored procedures from recalculating existing data that has not been changed.

On the Access front end the calculation stored procedures are called in a pass-through query. There is a macro that performs an OpenQuery on the pass-through query, then this macro is automatically run when the user closes the form (event: On Close) that's used for entering/editing/deleting records in the underlying InputData SQL table.

I've run some tests on this approach and it appears to be functioning OK.

Thanks to all contributors above for my first lesson on triggers.

sql

Changing a field value to NULL

I know the trick of using ctrl-0 to change it individually, but is there a
syntax that I can use as in the UPDATE to change a BUNCH of fields at the
same time. When changing fields I usually use the
UPDATE <table>
SET <field> = <parameter>
WHERE <field> = <parameter>
How do I do this to set a field as null where the parameter is 'No
Response'?
TIA
JCNever mind. I figured out what I was doing wrong. Dumb mistake.
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:4F1F5AC7-E93B-4E0D-BA69-13AE90A90F71@.microsoft.com...
>I know the trick of using ctrl-0 to change it individually, but is there a
>syntax that I can use as in the UPDATE to change a BUNCH of fields at the
>same time. When changing fields I usually use the
> UPDATE <table>
> SET <field> = <parameter>
> WHERE <field> = <parameter>
> How do I do this to set a field as null where the parameter is 'No
> Response'?
> TIA
> JC

Changing a field background color IIF.....

Hi All,
I need to change the backgroung color to a field in the background property....

=iif(Fields!act_shp_dt.Value <> Fields!prm_shp_dt.Value , " Late ","")

This is the field and I want it to change color if the statement is true. Can anyone help?

Thank you in advanced, Kerrie

You have a misunderstand.
You should put a label or something like that and in the backgroud propertie put the expression.
=iif(Fields!act_shp_dt.Value <> Fields!prm_shp_dt.Value , "Blue","Red")
That's all|||Thank You so much for your help. That did it!!!

Changing a column type with replication

I need to change a column that is a six character varchar
called meeting_date to a datetime value. My plan is to
create a table that stores a int value
and a datetime value. Copy the primary key (an identity
column) and converted the value to a datetime on the
insert. Disable or alter all objects, especially triggers,
that use the column meeting_date. Then drop the
meeting_date column and add the column as a datetime
value. Wait for replication to make the change. Update
the new datetime column meeting_date with the converted
datetime value joining on the identity column.
Reestablish all objects and update all applications that
use that the meeting_date column.
Will this work with replication ? Is there a better
way ? etc
We are using merge replication and the column has 200,000
values of a 3,900,000 row table.
It is fairly easy to convert meeting_date value itself to
datetime. I am a developer not a DBA so I do not know all
the nuances of replication.
You realize that replication prevents schema changes to tables that are
being replicated. You must drop the publication before you make your
change. Once your change is made, you must re-create the publication and
generate a new snapshot. You stated that this table contains 3.9 million
rows. Are there any other large tables in this publication? What is the
line speed between the publisher and the subscriber(s)? The point that I'm
trying to make here is that the resynchronization could take a very long
time.
How big is the database? Would it be practical to burn it to a CD or DVD
and physically transport it to the subscriber(s)? That might turn out to be
faster than trying to push out a new snapshot.
Also, you might want to investigate using sp_repladdcolumn and
sp_repldropcolumn.
Mike
"Phil396" <anonymous@.discussions.microsoft.com> wrote in message
news:502e01c52328$6d842d10$a601280a@.phx.gbl...
> I need to change a column that is a six character varchar
> called meeting_date to a datetime value. My plan is to
> create a table that stores a int value
> and a datetime value. Copy the primary key (an identity
> column) and converted the value to a datetime on the
> insert. Disable or alter all objects, especially triggers,
> that use the column meeting_date. Then drop the
> meeting_date column and add the column as a datetime
> value. Wait for replication to make the change. Update
> the new datetime column meeting_date with the converted
> datetime value joining on the identity column.
> Reestablish all objects and update all applications that
> use that the meeting_date column.
>
> Will this work with replication ? Is there a better
> way ? etc
> We are using merge replication and the column has 200,000
> values of a 3,900,000 row table.
> It is fairly easy to convert meeting_date value itself to
> datetime. I am a developer not a DBA so I do not know all
> the nuances of replication.

Changing a column type with replication

I need to change a column that is a six character varchar
called meeting_date to a datetime value. My plan is to
create a table that stores a int value
and a datetime value. Copy the primary key (an identity
column) and converted the value to a datetime on the
insert. Disable or alter all objects, especially triggers,
that use the column meeting_date. Then drop the
meeting_date column and add the column as a datetime
value. Wait for replication to make the change. Update
the new datetime column meeting_date with the converted
datetime value joining on the identity column.
Reestablish all objects and update all applications that
use that the meeting_date column.
Will this work with replication ? Is there a better
way ? etc
We are using merge replication and the column has 200,000
values of a 3,900,000 row table.
It is fairly easy to convert meeting_date value itself to
datetime. I am a developer not a DBA so I do not know all
the nuances of replication.You realize that replication prevents schema changes to tables that are
being replicated. You must drop the publication before you make your
change. Once your change is made, you must re-create the publication and
generate a new snapshot. You stated that this table contains 3.9 million
rows. Are there any other large tables in this publication? What is the
line speed between the publisher and the subscriber(s)? The point that I'm
trying to make here is that the resynchronization could take a very long
time.
How big is the database? Would it be practical to burn it to a CD or DVD
and physically transport it to the subscriber(s)? That might turn out to be
faster than trying to push out a new snapshot.
Also, you might want to investigate using sp_repladdcolumn and
sp_repldropcolumn.
Mike
"Phil396" <anonymous@.discussions.microsoft.com> wrote in message
news:502e01c52328$6d842d10$a601280a@.phx.gbl...
> I need to change a column that is a six character varchar
> called meeting_date to a datetime value. My plan is to
> create a table that stores a int value
> and a datetime value. Copy the primary key (an identity
> column) and converted the value to a datetime on the
> insert. Disable or alter all objects, especially triggers,
> that use the column meeting_date. Then drop the
> meeting_date column and add the column as a datetime
> value. Wait for replication to make the change. Update
> the new datetime column meeting_date with the converted
> datetime value joining on the identity column.
> Reestablish all objects and update all applications that
> use that the meeting_date column.
>
> Will this work with replication ? Is there a better
> way ? etc
> We are using merge replication and the column has 200,000
> values of a 3,900,000 row table.
> It is fairly easy to convert meeting_date value itself to
> datetime. I am a developer not a DBA so I do not know all
> the nuances of replication.

changing "header rows to skip" property in flat file connection during runtime

Hi all

I have a flat file.I am trying to set the value for the property "HeaderRowsToSkip" during runtime.I have set an expression for this in my "flat file connection manager". But this is not working.The connection manager is not able to take the value during runtime.

My expression is as follows:

DataRowsToSkip : @.[user:: Var]

where "Var" is my variable which gets the value from the rowcount component and trying to set it back to the "HeaderRowsToskip" property.

I ve even tried setting the value to the "HeaderRowsToSkip" property in the expression builder.

Its not working....

Can anyone help me out in solving this?

Thanks in advance

Regards

Suganya

Is the rowCont component part of the same data flow where your trying to write into the flat file? If so; there might be the problem (no 100% sure); but I am guessing that by the time the flat file connection manager gets 'configured' the rowcount variable has not been populated.

Try moving the logic of the row count to a different dataflow at the beginning of the control flow and see if that helps...

|||

I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?

And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.I get this doubt because the connection manager is able to skip the number of rows with the same expression being used and setting the value for the variable which am using in the expression during design time.

Thanks in advance.

Regards,

Suganya

|||

I've just had a go at this and it works fine.

Where is the rowcount component? Is it in the same data-flow?

-Jamie

|||

No...its not in the same dataflow task.As you said in the previous post i have changed it another data flow task.

My package has the following tasks and components

1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination

2.Dataflow task(2) containing a flat file and an OLE DB destination

Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).

am i doing anything wrong?

Regards,

Suganya

|||

All except one thing. Why have you got a an OLE DB Destination in the first data-flow? Where are you inserting the data to? You can terminate the data-flow with the rowcount compoennt - that seems to be all you need.

-Jamie

|||

Suganya Mahadevan wrote:

I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?

And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.

Yes; and the behavior you described proof that. The connection manager gets set up using the available values when the execution starts; at that point he only value available for the rowcount variable is the one given at design time; as no rows have started to flow. That is why I was suggesting moving the rowcount logic to a previous stage.

But I got a question for you; is the row count variable being populated based on the same file you are trying to skip the rows from? That would not cause to skip all the rows every time? (Unless you are using different files)

|||

Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

But the same scenario is working when i set the value during design time and i have specified this in my previous post.

|||

No its 2 different files.

My package has the following tasks and components

1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination

2.Dataflow task(2) containing a flat file and an OLE DB destination

Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

What is the problem with my package?

|||

Suganya Mahadevan wrote:

Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

But the same scenario is working when i set the value during design time and i have specified this in my previous post.

Could you set a break point in the second dataflow and then execute the package; when it breaks; could you check the value of the rowcount variable to see if it's properly populated?

Let's make sure first the variable is correctly populated...

|||

Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)

By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.

Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?

|||

Suganya Mahadevan wrote:

Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)

By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.

Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?

Put the breakpoint on the OnPreExecute event of the second data-flow.

When it breaks, drag the variable into a Watch window and you will be able to see the value.

-Jamie

|||

Thanks Jamie.The value is getting set.

But flat file is not skipping the number of rows.

Any solution for this?

Regards

Suganya

|||

Can you share the package and the source files? Paste them up here if so.

-Jamie

|||

It is not possible for me to share the package here.Can u give ur mail id so that i could send my package and source code?

-Suganya

changing "header rows to skip" property in flat file connection during runtime

Hi all

I have a flat file.I am trying to set the value for the property "HeaderRowsToSkip" during runtime.I have set an expression for this in my "flat file connection manager". But this is not working.The connection manager is not able to take the value during runtime.

My expression is as follows:

DataRowsToSkip : @.[user:: Var]

where "Var" is my variable which gets the value from the rowcount component and trying to set it back to the "HeaderRowsToskip" property.

I ve even tried setting the value to the "HeaderRowsToSkip" property in the expression builder.

Its not working....

Can anyone help me out in solving this?

Thanks in advance

Regards

Suganya

Is the rowCont component part of the same data flow where your trying to write into the flat file? If so; there might be the problem (no 100% sure); but I am guessing that by the time the flat file connection manager gets 'configured' the rowcount variable has not been populated.

Try moving the logic of the row count to a different dataflow at the beginning of the control flow and see if that helps...

|||

I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?

And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.I get this doubt because the connection manager is able to skip the number of rows with the same expression being used and setting the value for the variable which am using in the expression during design time.

Thanks in advance.

Regards,

Suganya

|||

I've just had a go at this and it works fine.

Where is the rowcount component? Is it in the same data-flow?

-Jamie

|||

No...its not in the same dataflow task.As you said in the previous post i have changed it another data flow task.

My package has the following tasks and components

1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination

2.Dataflow task(2) containing a flat file and an OLE DB destination

Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).

am i doing anything wrong?

Regards,

Suganya

|||

All except one thing. Why have you got a an OLE DB Destination in the first data-flow? Where are you inserting the data to? You can terminate the data-flow with the rowcount compoennt - that seems to be all you need.

-Jamie

|||

Suganya Mahadevan wrote:

I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?

And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.

Yes; and the behavior you described proof that. The connection manager gets set up using the available values when the execution starts; at that point he only value available for the rowcount variable is the one given at design time; as no rows have started to flow. That is why I was suggesting moving the rowcount logic to a previous stage.

But I got a question for you; is the row count variable being populated based on the same file you are trying to skip the rows from? That would not cause to skip all the rows every time? (Unless you are using different files)

|||

Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

But the same scenario is working when i set the value during design time and i have specified this in my previous post.

|||

No its 2 different files.

My package has the following tasks and components

1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination

2.Dataflow task(2) containing a flat file and an OLE DB destination

Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

What is the problem with my package?

|||

Suganya Mahadevan wrote:

Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

But the same scenario is working when i set the value during design time and i have specified this in my previous post.

Could you set a break point in the second dataflow and then execute the package; when it breaks; could you check the value of the rowcount variable to see if it's properly populated?

Let's make sure first the variable is correctly populated...

|||

Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)

By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.

Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?

|||

Suganya Mahadevan wrote:

Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)

By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.

Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?

Put the breakpoint on the OnPreExecute event of the second data-flow.

When it breaks, drag the variable into a Watch window and you will be able to see the value.

-Jamie

|||

Thanks Jamie.The value is getting set.

But flat file is not skipping the number of rows.

Any solution for this?

Regards

Suganya

|||

Can you share the package and the source files? Paste them up here if so.

-Jamie

|||

It is not possible for me to share the package here.Can u give ur mail id so that i could send my package and source code?

-Suganya

sql

Changind a Primary Key value

Hi,
I have a table with a Primary Key field that is an integer (int) data type
with auto increment.
Is there a way to change the value of the ID field (e.g. currently it is 100
and I want to change it to 20, is it possible?)
Thanks,
Shai,shalom
CREATE TABLE Test (col INT IDENTITY(1,1))
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--4 rows
GO
DBCC CHECKIDENT (Test, RESEED, 2)
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--6 rows
GO
DROP TABLE Test
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>
|||Shai
You will get an error of primary key violention. This is one of many reasons
not to use an identity property as a primary key,
as you cannot update it.
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>
|||On Thu, 5 Aug 2004 14:55:45 +0200, "Shai Goldberg"
<gshai(Remove-it)@.shamir.co.il> wrote:

>Hi,
>I have a table with a Primary Key field that is an integer (int) data type
>with auto increment.
>Is there a way to change the value of the ID field (e.g. currently it is 100
>and I want to change it to 20, is it possible?)
>Thanks,
>
Hi Shai,
Do you mean that after inserting data, you want to manually change the ID
for one of the rows inserted?
Unfortunately, that is not possible.
From Books Online:
Error 8102
Severity Level 16
Message Text
Cannot update identity column '%.*ls'.
Explanation
You have specifically attempted to alter the value of an identity column
in the SET portion of the UPDATE statement. You can only use the identity
column in the WHERE clause of the UPDATE statement.
Action
Updating of the identity column is not allowed. To update an identity
column, you can use the following techniques:
To reassign all identity values, bulk copy the data out, and then drop and
re-create the table with the proper seed and increment values. Then bulk
copy the data back into the newly created table. When bcp inserts the
values it will appropriately increase the values and redistribute the
identity values. You can also use the INSERT INTO and sp_rename commands
to accomplish the same action.
To reassign a single row, you must delete the row and insert it using the
SET IDENTITY_INSERT tblName ON clause.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Something like this will work
drop table t
go
create table t( id int identity(1,1) primary key, data varchar(8) not null)
go
insert into t values('HI')
go
select * from t
GO
set identity_insert t on
insert t (id, data) select 2, data from t where id = 1
delete from t where id = 1
GO
set identity_insert t off
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>
sql