January 20, 2010 at 8:43 am
We transfered a database from a SQL2000 (Windows Server 2003) environment to a SQL2008 (Windows Server 2008).
I made a trigger in you see below.
SQL2000:
When I update field "fiattering" from 'N' to 'J' I get: 2 times "1 rows affected"
SQL2008:
When I update field "fiattering" from 'N' to 'J' I get: 1 time "0 rows affected" and 1 time "1 rows affected"
What is the cause that of the "0 rows affected"
It can be a security option because we had to configure SQL2008.
create TRIGGER [dbo].[_ACT_orkrg_UPDATE]
ON [dbo].[orkrg]
FOR UPDATE, INSERT
as
declare @fiattering_old as char (1)
declare @fiattering_new as char (1)
declare @ordernr as char (8)
declare @selcode as char (2)
select @ordernr=ltrim(rtrim(ordernr)), @selcode=selcode, @fiattering_new=fiattering
from inserted
select @fiattering_old=fiattering
from deleted
---------------------
if
@selcode= '1 '
and
@fiattering_old = 'N'
and @fiattering_new = 'J'
BEGIN
update orkrg set refer1 = 'XX' where ordernr = @ordernr
END
January 20, 2010 at 9:29 am
Just one question
h.wijnbergen (1/20/2010)
select @ordernr=ltrim(rtrim(ordernr)), @selcode=selcode, @fiattering_new=fiatteringfrom inserted
select @fiattering_old=fiattering
from deleted
What happens if there's more than one row in the inserted/deleted tables?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 21, 2010 at 6:39 am
I agree with Gail. What happens if more than 1 row is inserted or updated.
I wouldn't worry about the messages if you can verify that the correct changes are made to the data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 21, 2010 at 11:02 pm
I guess some condition is getting failed in ur trigger.So the no of rows effectd is "0" .
Regards,
Saravanan
January 22, 2010 at 4:52 am
I changed
update orkrg set refer1 = 'XX' where ordernr = @ordernr
in:
update orkrg set refer1 = 'XX' where ltrim(rtrim(ordernr)) = @ordernr
And now it works, anyway thanks for your comments.
Henk-Jan
January 22, 2010 at 4:57 am
h.wijnbergen (1/22/2010)
And now it works, anyway thanks for your comments.
It's still not going to work properly if you insert or update more than one row at a time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2010 at 5:28 am
Thnx Gail,
The field ordernr is unique, so it can't be more then one value.
January 22, 2010 at 5:49 am
Doesn't matter how unique the fields are.
I'm not talking about the update in the trigger, I'm talking about the insert/update that fires the trigger.
If anyone ever inserts or updates more than one row in a single statement, that trigger will not work correctly. You are assuming that there is 1 and only ever 1 row in the inserted/deleted tables. That is not the case. The inserted and deleted tables contain all the rows affected by an operation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2010 at 5:57 am
As Gail already stated, you should implement this trigger to be able to cope with more than one row in the inserted and/or deleted internal objects !
e.g
--TEST IT -- TEST IT --
BEGIN
update O
set refer1 = 'XX'
from orkrg O
inner join inserted I
on I.ordernr = O.ordernr
AND I.fiattering = 'J'
inner join deleted D
on D.ordernr = O.ordernr
AND D.fiattering = 'N'
END
ps I presume ordernr is a unique key in your table and is properly indexed !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 22, 2010 at 2:30 pm
There are many things wrong with this trigger:
1) as others have mentioned it doesn't handle more than 1 row. You say ordernr is unique - so what. How about this:
update
set ...
where ordernr in ('val1', 'val2', 'val3')
inserts can also insert more than one row in a statement.
I sure hope your application (and every single person/entity that has access to the database) NEVER updates or inserts more than one row or you have lost information with this type of trigger.
2) no error handling
3) seems a bit recursive since it updates the table it fires from. This can get you in trouble, and I also think there is a setting that controls this??
4) do you really need the ltrim(rtrim(?
5) @fiattering_old will always be NULL (and thus your IF will always fail) if this is an INSERT. Hope that is what you intend.
6) There is no IF UPDATE. Do you intend this to fire and do it's work no matter what field is actually updated?
7) update orkrg... - object not referenced by it's schema.
8) as someone else posted this can be done in a single statement with no variables at all and it will properly handle multiple rows as well.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 23, 2010 at 12:55 am
TheSQLGuru (1/22/2010)
3) seems a bit recursive since it updates the table it fires from. This can get you in trouble, and I also think there is a setting that controls this??
There is, called something like 'recursive triggers', and is false by default.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply