FOR insert/update/delete trigger

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

FOR insert/update/delete trigger

Author

Topic

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2010-12-17 : 20:31:28

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

Sachin.Nand

2937 Posts

Posted - 2010-12-18 : 00:41:13

I think you should go for a computed column.
Make the column duration as computed and put the formula for it as DATEDIFF(month, lockin_date , getdate()).

PBUH

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2010-12-18 : 04:29:07

Thank you very much for reply mate..your solution will work only when adding new data..but I already have data in the table so I don't think computed column will work with it..what say?

Sachin.Nand

2937 Posts

Posted - 2010-12-18 : 04:48:20

Nope it will work.Try the below query


create table test(
		id int  identity(1,1),
		dt1 datetime NULL,
		dt2  AS (datediff(dd,dt1,getdate()))
	)


insert into test(dt1)
select '10-jan-2010'

select * from test

update test set dt1='31-jan-2010' where id=1

select * from test

drop table test

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources