Still having issues with triggers

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

General SQL Server Forums

New to SQL Server Programming

Still having issues with triggers

Author

Topic

tvb2727
Starting Member

35 Posts

Posted - 2010-10-19 : 21:00:18

I need to make a trigger where I need to verify the updated purchase price is not less that what is already in the table. I tried to use an INSTEAD OF and was able to pull the value out of the table(before the update) and make the error happen, but if its correct and greater than what is already in the table it doesn't make the update. If you use a FOR then the updated is made before I can pull the earlier value out of the database to verify if its less than etc. Any ideas how to accomplish this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-19 : 21:28:58

You have to put the UPDATE statement in the INSTEAD OF trigger. INSTEAD OF means you are going to handle the update, so you have to include it in the code.

Show us what you've got.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tvb2727
Starting Member

35 Posts

Posted - 2010-10-19 : 21:43:34

Here is the code. Also, what if I have other triggers on the same table. Those will hit the update on this trigger and not work properly. Does that makes sense. I tried to do a FOR trigger that works fine, but it wouldn't even do a normal update because it was hitting the ELSE of the INSTEAD OF trigger. Please help if possible. I would be appreciated.

Create TRIGGER price-trig
ON Prices
Instead OF UPDATE
AS
declare @pnum int
declare @pprice1 decimal(10, 1)
declare @pprice2 decimal(10, 1)
select @pnum = inserted.PRICE_NUM from inserted
select @pprice1 = (select P_Price from pricemain where PRICE_NUM = @pnum)
select @pprice2 = inserted.P_Price from inserted

if (@pprice2 < @pprice1)
begin
rollback transaction
Print 'Purchase Price can not be less than already in the Table!'
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-19 : 23:16:38

Your INSTEAD OF trigger is missing the UPDATE statement. You have to perform in there as you are overriding the initial UPDATE query.

You should really rethink your design if you have multiple triggers per table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2010-10-20 : 00:42:52

multiple triggers per table. that's side-effect city!

elsasoft.org

tvb2727
Starting Member

35 Posts

Posted - 2010-10-20 : 15:14:40

Answer to my issue:

select @pprice1 = deleted.P_Price from deleted

returns what was in there before the update went through with the FOR UPDATE statement.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-20 : 15:27:36

Be warned that code like that is a major design flaw. See this for more info: http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tvb2727
Starting Member

35 Posts

Posted - 2010-10-21 : 01:46:16

For my case, it should be fine. Thanks for the information though for future references.

Kristen
Test

22859 Posts

Posted - 2010-10-21 : 07:11:06

"it should be fine"

Scary assumption!

At the very least do:


IF SELECT COUNT(*) FROM deleted <> 1
BEGIN
   raiserror(...
   ROLLBACK
   ...
END

Consider a manual

UPDATE MyTable
SET SomeColumn = ... some fixed value ...

where you need to fix up the table en-masse. The trigger will fire ONCE to process all the records in one go. Chances are that you, or another DBA/DEV, will do that at some time ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources