Trigger Query - SQL Server Forums

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 Query

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2012-03-23 : 12:44:52

Hi

I've only just started using triggers so apologies for my lack of knowledge on this area, all i wanted to know if you can fire a trigger on a particular value of a field?

I have successfully implemented a trigger below which works when the field is changed which is okay, but I want it only to fire when the status field is 'Registered'. There are other values that it can be but I only want it to fire when the status is 'Registered'

CREATE TRIGGER Reset_TimesOut
ON Supplier
FOR UPDATE as
IF UPDATE(Status)
BEGIN
UPDATE a
SET Renew_Alloc = 0
FROM Account a, Deleted d, Inserted i
WHERE d.accountid = a.cccountid
END

Just to note the update is required on the account table where the status is on the supplier table which have both have an accountid primary key.

Can anybody help me on this as i'm sure it must be achievable.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-23 : 13:37:14

the update trigger will ALWAYS FIRE when an update occurs.

You can however use logic in the trigger to only do what you want..and nothing if the condition isn't met

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-23 : 13:42:16

How about this...and you need to understand that triggers work on sets of data at a time



CREATE TRIGGER Reset_TimesOut ON Supplier
	FOR UPDATE as
		BEGIN
			UPDATE a
			   SET Renew_Alloc = 0
			  FROM Account	   a 
	            INNER JOIN Inserted	   i
			    ON a.accountid = i.acccountid
			 WHERE i.Status    = 'Registered'
		END

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

bendertez
Yak Posting Veteran

94 Posts

Posted - 2012-03-26 : 05:32:10

Hi Brett

Thanks for the reply.

I kind of thought that it might be something like what you have written and implementing this seems to work perfectly when the status is set to "Registered"

However when I set anything other than "Registered" it falls overs with an error of:

"Row cannot be located for updating. Some values may have been changed sinc it was last read."

This seems to be a VB error message, but I don't understand why it works when the status is "Registered" but it falls over when it is something elese. I thought from the code that the trigger wouldn't update anything unless the status was "Registered"?

Do you have any idea why it is doing this?

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-26 : 09:21:33

Are you using mySQL?

This is a SQL Server Board

If you are, then try here

http://www.dbforums.com/mysql/

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources