A Trigger on a specific column update

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

A Trigger on a specific column update

Author

Topic

sarorelasoul
Starting Member

29 Posts

Posted - 2008-11-05 : 15:21:22

Hello,

It's my first time to use triggers so Is there a way to create a trigger when a specific column gets updated(not the whole table)?

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-05 : 15:47:20

[code]CREATE TRIGGER trigger_name ON tablename
FOR UPDATE
AS
SET NOCOUNT ON
IF ( UPDATE(Column1) OR UPDATE(Column2))
BEGIN
your sql here
END[/code]

Webfred

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

sarorelasoul
Starting Member

29 Posts

Posted - 2008-11-06 : 11:35:22

Thanks a lot, it worked
but I have another question, I'm now creating a FOR INSERT Trigger that checks on the coll_desc field and updates it's value as follows

Create trigger AddCollegeOfToCollDescOnInsert on Program_Map_full
for insert
as
declare @inserted_coll_desc as nvarchar(255)
set @inserted_coll_desc = (select coll_desc from inserted)
if @inserted_coll_desc not like 'College of%'
or @inserted_coll_desc!='Institute of Aviation'
or @inserted_coll_desc!='School of Social work'
update Program_Map_Full set coll_desc='College of '+coll_desc

I know this is not working, how can I update the inserted value only?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 11:43:35

the problem with your code is you assume inserted will contain only a single record but its not always the case. so make it like below

Create trigger AddCollegeOfToCollDescOnInsert on Program_Map_full
for insert 
as
begin
update p
set coll_desc='College of '+coll_desc
from Program_Map_Full p
inner join inserted i
on i.primarykey=p.primarykey
where i.coll_desc not like 'College of%'
or i.coll_desc<>'Institute of Aviation'
or i.coll_desc<>'School of Social work'
end
primarykey is primary key of Program_Map_Full table

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-06 : 11:47:32

Attention!
NOT OR NOT is always TRUE!


where i.coll_desc not like 'College of%'
or i.coll_desc<>'Institute of Aviation'
or i.coll_desc<>'School of Social work'

I think the ORs must replaced by ANDs...

Webfred

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 11:50:33

quote:
Originally posted by webfred

Attention!
NOT OR NOT is always TRUE!

where i.coll_desc not like 'College of%'
or i.coll_desc<>'Institute of Aviation'
or i.coll_desc<>'School of Social work'

I think the ORs must replaced by ANDs...

Webfred

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

i didnt correct it as i'm not sure of OPs requirement

sarorelasoul
Starting Member

29 Posts

Posted - 2008-11-10 : 21:19:37

What if this table doesn't have a primary key?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 22:57:38

then take those columns by which you can uniquely identify a row in table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources