Help Creating a Trigger

These triggers are killing me, too new, I hope someone can help.
I need a trigger that will fire if data in a table is changed and if so
update another table;
Like: if fieldtable changes update trackingtable where fieldtable.ID =
trackingtable.IDLook into the trigger keyword/function UPDATE()
You might also consider a cascading update in a pk/fk...
<pasterto@.hotmail.com> wrote in message
news:1123103443.926156.216160@.g47g2000cwa.googlegroups.com...
> These triggers are killing me, too new, I hope someone can help.
> I need a trigger that will fire if data in a table is changed and if so
> update another table;
> Like: if fieldtable changes update trackingtable where fieldtable.ID =
> trackingtable.ID
>|||Something like this:
create table table1
(
table1Id int primary key,
value varchar(10)
)
insert into table1 values (1,'value')
insert into table1 values (2,'value')
insert into table1 values (3,'value')
insert into table1 values (4,'value')
go
create table table2
(
table2Id int primary key,
value varchar(10),
table1Id int foreign key references table1 (table1Id)
)
insert into table2 values (10,'value',1)
insert into table2 values (20,'value',1)
insert into table2 values (30,'value',2)
insert into table2 values (40,'value',3)
go
create trigger trTable1_update
on table1
after update
as
update table2
set value = inserted.value
from table2
join inserted
on table2.table1Id = inserted.table1Id
go
update table1
set value = 'newval'
where table1Id = 1
select * from table2
update table1
set value = 'allnew'
select * from table2
This is the lean version, and will need error handling and such, so if you
need info about that, let us know!
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
<pasterto@.hotmail.com> wrote in message
news:1123103443.926156.216160@.g47g2000cwa.googlegroups.com...
> These triggers are killing me, too new, I hope someone can help.
> I need a trigger that will fire if data in a table is changed and if so
> update another table;
> Like: if fieldtable changes update trackingtable where fieldtable.ID =
> trackingtable.ID
>|||THANK YOU... !!!
underscored and understated.|||See here you're running an update on everything not just changed rows. That
will get expensive quickly. I personally would use a cascade update for
this task, but if that doesn't work, I would only update values in table2
that have a corrosponding value in the inserted table that has
UPDATE(value)...
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uCYXpEHmFHA.3832@.TK2MSFTNGP10.phx.gbl...
> Something like this:
> create table table1
> (
> table1Id int primary key,
> value varchar(10)
> )
> insert into table1 values (1,'value')
> insert into table1 values (2,'value')
> insert into table1 values (3,'value')
> insert into table1 values (4,'value')
> go
> create table table2
> (
> table2Id int primary key,
> value varchar(10),
> table1Id int foreign key references table1 (table1Id)
> )
> insert into table2 values (10,'value',1)
> insert into table2 values (20,'value',1)
> insert into table2 values (30,'value',2)
> insert into table2 values (40,'value',3)
> go
> create trigger trTable1_update
> on table1
> after update
> as
> update table2
> set value = inserted.value
> from table2
> join inserted
> on table2.table1Id = inserted.table1Id
> go
> update table1
> set value = 'newval'
> where table1Id = 1
>
> select * from table2
> update table1
> set value = 'allnew'
> select * from table2
>
> This is the lean version, and will need error handling and such, so if you
> need info about that, let us know!
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> <pasterto@.hotmail.com> wrote in message
> news:1123103443.926156.216160@.g47g2000cwa.googlegroups.com...
>|||What?

This only modifies the table2 rows that match the join. Otherwise rows that
didn't match would end up NULL, right?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Ben" <ben@.online.nospam> wrote in message
news:57a4f$42f13aec$d8445835$17008@.FUSE.NET...
> See here you're running an update on everything not just changed rows.
> That will get expensive quickly. I personally would use a cascade update
> for this task, but if that doesn't work, I would only update values in
> table2 that have a corrosponding value in the inserted table that has
> UPDATE(value)...
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uCYXpEHmFHA.3832@.TK2MSFTNGP10.phx.gbl...
>|||IF UPDATE() doesn't tell you what rows changed. It tells you what
*columns* were referenced in the SET part of the UPDATE. The IF
UPDATE() is pretty useless IMO. If you really want to know what values
changed then join to the INSERTED table as Louis did and compare the
values in the join.
David Portas
SQL Server MVP
--|||I guess it boils down to what constitutes a data change. I'm stuck with a
one field change to one field. So to me, if you have a row with fields 1, 2
and 3, and you want to cascade updates to field 2 to a different table, it
doesn't make sense to update the second table if only field 1 has been
altered in the original table. This is just an assumption I'm making, which
is probably wrong.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1123107560.009756.66460@.o13g2000cwo.googlegroups.com...
> IF UPDATE() doesn't tell you what rows changed. It tells you what
> *columns* were referenced in the SET part of the UPDATE. The IF
> UPDATE() is pretty useless IMO. If you really want to know what values
> changed then join to the INSERTED table as Louis did and compare the
> values in the join.
> --
> David Portas
> SQL Server MVP
> --
>|||Also as a follow-up, the inserted table in a trigger will contain all
columns for all of the updated rows not just the columns that were updated.
UPDATE() will tell you which of those colums have been updated for which
records.
For example, the update method i'm talking about would look something
like...
UPDATE table2
SET value = inserted.value
FROM table2
INNER JOIN inserted ON table2.table1Id = inserted.table1Id
WHERE UPDATE(value)
And if there are multiple columns being updated I would do the following...
UPDATE table2
SET valueN = inserted.valueN
FROM table2
INNER JOIN inserted ON table2.table1Id = inserted.table1Id
WHERE UPDATE(valueN)
I don't think you need to/can qualify value inside UPDATE it assumes
inserted...
"Ben" <ben@.online.nospam> wrote in message
news:cb8ef$42f14f04$d8445835$26404@.FUSE.NET...
>I guess it boils down to what constitutes a data change. I'm stuck with a
>one field change to one field. So to me, if you have a row with fields 1,
>2 and 3, and you want to cascade updates to field 2 to a different table,
>it doesn't make sense to update the second table if only field 1 has been
>altered in the original table. This is just an assumption I'm making,
>which is probably wrong.
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1123107560.009756.66460@.o13g2000cwo.googlegroups.com...
>|||Oh, I see. You meant rows that changed, not just updated ones. I always
just do it as a single update statement. An improvement might be to do
something like:

and table2.value <> inserted.value
As for conditional execution, any opinion on which is better?
create trigger trTable1_update
on table1
after update
as
if update(value)
begin
update table2
set value = inserted.value
from table2
join inserted
on table2.table1Id = inserted.table1Id
end
update table2
set value = inserted.value
from table2
join inserted
on table2.table1Id = inserted.table1Id
where update(value)
go
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23IhRpdHmFHA.320@.TK2MSFTNGP09.phx.gbl...
> What?
>
> This only modifies the table2 rows that match the join. Otherwise rows
> that didn't match would end up NULL, right?
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Ben" <ben@.online.nospam> wrote in message
> news:57a4f$42f13aec$d8445835$17008@.FUSE.NET...
>