Triggers - 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 2000 Forums

SQL Server Development (2000)

Triggers

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-23 : 10:39:06

Ganesh writes "Greetings,

I like to write a trigger after i update a particular record.

I have a table which has products. I will select the "id" and update some of the fields (Using ASP & Sql server). In this process i have to make the first letter of the fields to be caps. For this i wrote a trigger where by to pass the ID which i am updating i used the query which follows.

select @maxid=id from inserted.

Find below the code whih i had used to fire the trigger.

create trigger pr_update
on products
for update
as
begin
DECLARE @maxid varchar(20),@loginname varchar(100),@headline varchar(200)
select @maxid = id from inserted
select @headline=headline from products where id=@maxid

if (@headline) is not null
update products set headline=UPPER(LEFT(headline, 1)) + LOWER(right(headline,(LEN(headline) - 1)))
where id= @maxid
end

When i create this trigger and if i update a field from my front end the changes are not saved to the database.

If i drop this trigger and make changes it gets saved and changes are reflected for the field which i made changes

What will be the problem?

Regards,
Ganesh"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-05-23 : 10:47:40

Probably you are getting an error in the trigger.
You should check the error handling as it sounds like you are not detecting the error.

In the trigger you should probably be using @headline for the update - that's maybe where the error lies.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-23 : 10:50:10

quote:
if (@headline) is not null
update products set headline=UPPER(LEFT(headline, 1)) + LOWER(right(headline,(LEN(headline) - 1)))
where id= @maxid
end

try
update products set headline=UPPER(LEFT(@headline, 1)) + LOWER(right(@headline,(LEN(@headline) - 1)))

key not is the @'s

Bleh, sniped again.. I type to slow

Edited by - M.E. on 05/23/2002 10:50:44

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-23 : 10:56:13

this seems a bit odd:
select @headline=headline from products where id=@maxid
why use the current value and not the updated one?

You could try this:
update product
set headline = UPPER(LEFT(i.headline, 1)) + LOWER(right(i.headline,(LEN(i.headline) - 1)))
FROM product INNER JOIN inserted i ON product.id = i.id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources