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)

Update Trigger

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-05-10 : 14:27:43

Trying to create a trigger that counts the records in a table that have a certain type and updates a field into the record that was just inserted.

I know this code does not work, but I think it will give you an idea on what I'm trying to accomplish:


CREATE TRIGGER [UpdateFreeField2] ON [dbo].[absences] 
AFTER insert
AS

select count(*) as xcount
from absences
where absences.type = '501'

update absences
set freenumberfield_02 = xcount
from inserted join absences on inserted.ID = absences.ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-10 : 14:32:29

[code]CREATE TRIGGER [UpdateFreeField2] ON [dbo].[absences]
AFTER insert
AS
update a
set a.freenumberfield_02 = a1.xcount
from absences a
join inserted i
on i.ID = a.ID
join (select type,count(*) as xcount
from absences
group by type) a1
on i.type = a1.type
and i.type='501'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources