after update trigger - 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)

after update trigger

Author

Topic

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2010-12-17 : 20:29:26

hi..I want to create a trigger which will update a particular column of the table

e.g i have this table named "investors"..when either of INSERT,UPDATE,DELETE command takes place on any of the records of the entire table, then 'duration' column should be calculated and updated for each record

duration = datediff(month,lockin_date,getdate())

Can somebody tell me how to write a trigger for it..
I have written the following trigger but it is not working

CREATE TRIGGER [dbo].[investors_update]
ON [dbo].[investors]
FOR INSERT,UPDATE,DELETE
AS
DECLARE
@diff int

BEGIN

select @diff = select DATEDIFF(month, lockin_date , getdate()) from investors
update investors set duration=@diff

END
go

bobmcclellan
Starting Member

46 Posts

Posted - 2010-12-17 : 23:19:29

this is untested but should get you there Rockstar...
Note: hopefully you are testing this in a development environment.
Your update query had no params. if it worked you would have updated your whole table.

CREATE TRIGGER [dbo].[investors_update]
ON [dbo].[investors]
FOR INSERT,UPDATE,DELETE
AS

BEGIN

DECLARE @diff int, @id int

set @id = (Select id from inserted)

select @diff = (select DATEDIFF(month, i.lockin_date , getdate())
from investors inv
inner join Inserted i on inv.id = i.id )

update investors
set duration=@diff
where id = @id

END

Sachin.Nand

2937 Posts

Posted - 2010-12-18 : 02:09:30

DONT CROSS POST.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=154334

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources