Trigger updates other accounts

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trigger updates other accounts

Author

Topic

damdeok
Starting Member

39 Posts

Posted - 2011-06-28 : 06:03:20

I created this trigger:

CREATE TRIGGER GensIncentives
ON [Character]
FOR UPDATE AS
UPDATE [Character]
SET [MULGensIncentives]=[MULGensIncentives]+100
WHERE [cLevel]=400 AND Resets BETWEEN 1 AND 131
GO

but when I do an sql query on 1 account the trigger runs on other accounts as well where it has already a cLevel=400,

update Character set cLevel=400 where name='John'

Result:


name	MULGensIncentives	cLevel	Resets
John	100			400		10
Paul	100			400		50
Mary	100			400		20
Mark	100			400		15
Peter	100			400		11

What is the correct trigger code?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-28 : 06:51:56

Read about triggers.
There are "temp" tables INSERTED and DELTED available during runtime of the trigger.
With these you are able to hit only desired rows.

No, you're never too old to Yak'n'Roll if you're too young to die.

damdeok
Starting Member

39 Posts

Posted - 2011-06-28 : 07:19:33

Thanks.

Anyone with a direct solution?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-28 : 07:50:11

With a direct solution you mean ANYONE should do YOUR work?

Maybe Mrs. and Mr. Anyone are in a Holiday...

No, you're never too old to Yak'n'Roll if you're too young to die.

damdeok
Starting Member

39 Posts

Posted - 2011-06-28 : 08:15:02

Maybe you can help. I've been reading about Trigger the whole night. I can't figure how to update 1 account without affecting other accounts.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-28 : 08:22:26

Hope that I understand correct what you need...
Try this:

-- I guess that, in your example, the name column is the column to identify a specific row...
CREATE TRIGGER GensIncentives
ON [Character]
FOR UPDATE AS
UPDATE c
SET [MULGensIncentives]=c.[MULGensIncentives]+100
FROM [Character] c
JOIN INSERTED i 
ON  i.name = c.name
AND i.[cLevel]=400 
AND i.Resets BETWEEN 1 AND 131
GO

No, you're never too old to Yak'n'Roll if you're too young to die.

damdeok
Starting Member

39 Posts

Posted - 2011-06-28 : 08:40:23

Thanks. I think that do what I needed. I'm going to test it more.

The problem with trigger is it update an account even if I don't intend to use it.

Example is if I only change the Resets of a Character and it goes inside the trigger criteria.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources