Microsoft SQL Server forum

Author

Trigger?

Bethany Holliday

2006-01-24, 1:23 pm

Hi All,

I have been working with SQL Server for some time - but until now
haven't tried to implement a trigger and am not sure if this is an
appropriate use. Any advice would be greatly appreciated.

I have this table: almis.dbo.aadata_x

when almis.dbo.aadata_x is updated, I want to automatically be able to
update a table in another database using this sql (only pulling
specifics from this table - not everything):

insert into dbname.dbo.tablename
select stfips as statefips, right(area, 3) as countyfips, periodyear,
race, gender, cenoccgrp, population, laborforce, emplab, unemp,
unemprate, pctpop, pctlabforce, pctemplab, pctunemp, pcttotlabforce,
pcttotemplab, pcttotunemp from aadata_x
where areatype = 04 and periodyear = 2000
and stfips = 18

I apologize in advance if this is too basic of a question -- but this is
something I need to be able to do in many cases .. on update of table1
.. update certain fields in another table - perhaps in the same
database, perhaps not.

Again - thanks for any input you might be able to give me.

B

*** Sent via Developersdex http://www.droptable.com ***

MGFoster

2006-01-24, 8:24 pm

Bethany Holliday wrote:
> Hi All,
>
> I have been working with SQL Server for some time - but until now
> haven't tried to implement a trigger and am not sure if this is an
> appropriate use. Any advice would be greatly appreciated.
>
> I have this table: almis.dbo.aadata_x
>
> when almis.dbo.aadata_x is updated, I want to automatically be able to
> update a table in another database using this sql (only pulling
> specifics from this table - not everything):
>
> insert into dbname.dbo.tablename
> select stfips as statefips, right(area, 3) as countyfips, periodyear,
> race, gender, cenoccgrp, population, laborforce, emplab, unemp,
> unemprate, pctpop, pctlabforce, pctemplab, pctunemp, pcttotlabforce,
> pcttotemplab, pcttotunemp from aadata_x
> where areatype = 04 and periodyear = 2000
> and stfips = 18
>
> I apologize in advance if this is too basic of a question -- but this is
> something I need to be able to do in many cases .. on update of table1
> . update certain fields in another table - perhaps in the same
> database, perhaps not.

CREATE TRIGGER trigger_name
ON table
AFTER UPDATE
AS

insert into dbname.dbo.tablename
select stfips as statefips, right(area, 3) as countyfips, periodyear,
race, gender, cenoccgrp, population, laborforce, emplab, unemp,
unemprate, pctpop, pctlabforce, pctemplab, pctunemp, pcttotlabforce,
pcttotemplab, pcttotunemp
from aadata_x
where areatype = 04 and periodyear = 2000 and stfips = 18

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Sponsored Links