IF UPDATE trigger with RAISERROR

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)

IF UPDATE trigger with RAISERROR

Author

Topic

ischenk
Starting Member

15 Posts

Posted - 2008-07-22 : 19:13:44

Can someone tell me how to correct this code? I am trying to get this to trigger to print out a message when this table is updated. the error I'm getting is:

Msg 156, Level 15, State 1, Procedure Up_Prod, Line 10
Incorrect syntax near the keyword 'select'.
Msg 156, Level 15, State 1, Procedure Up_Prod, Line 11
Incorrect syntax near the keyword 'select'.

The code I used was this:

use Northwind
go
create trigger Up_Prod
on dbo.Products
for update
as
if update (UnitsInStock)
begin
declare @string varchar(100)
declare @PName varchar (20)
declare @Units varchar (5)
set @PName = select ProductName from inserted
set @Units = select UnitsInStock from inserted
set @string = @PName + '' + 'now has' + '' + @Units + 'units.'
raiserror (@string, 10, 1)
end

I'm still new to triggers and most SQL code for that matter. Any help would be greatly appriciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-22 : 19:18:38

You are not coding your trigger properly to handle multiple rows. See this blog post of mine: http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

But to answer your question, it should be:
select @PName = ProductName from inserted
select @Units = UnitsInStock from inserted

You shouldn't be doing this in a trigger though. Your application should be handling this.

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

Subscribe to my blog

ischenk
Starting Member

15 Posts

Posted - 2008-07-22 : 19:26:08

Thanks, tkizer. I know that I shouldn't be doing it like this, but they wanted to see it in a trigger form. I am a moron. I should have done it just like you have it. However, I did find out that putting the select statement in Parenthesis also works. Thanks for your help. You really do know your stuff.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources