Re: Best way to update other tables when a new record is addedotedited

Correct.

Something like this..(*Disclaimer, code is off the top of my head, there are
probably syntax errors)

create trigger MyTable_Update on dbo.MyTable
for update
as

SET NOCOUNT ON /* Suppressed the " Rows affected" message */
-- you can check each individual field with the Update statement to see of
it is part of the delta, see Books Online for more info
if UPDATE(MyField)
begin
if Exists(Select
I.MyKeyField
From
Inserted I
JOIN dbo.SomeOtherTable T ON T.MyKeyField =
I.MyKeyField)
begin
Update dbo.SomeOtherTable
SET MyOtherField = I.MyField
From
dbo.SomeOtherTable T
JOIN Inserted I ON ON T.MyKeyField = I.MyKeyField
end
else
begin
Insert dbo.SomeOtherTable
(MyKeyField, MyOtherField)
Select MyKeyField, MyField
From Inserted
end
end


See Books Online for more information about MSSQL's Inserted/Deleted temp
tables used in triggers

Good luck,
krf

"Rhea Grason" <Rhea.Grason@xxxxxxxxxxxxxxxxxx> wrote in message
news:460bea89@xxxxxxxxxxxxxxxxxxxxxxxxx
I am not that familiar with SQL Server but I will try using a trigger.
Would I be correct in assuming that if the insert or update of the record
is not successful the trigger will also not succeed? Thanks.

Bill Todd<no@xxxxxx> 3/27/2007 5:07 PM >>>
Rhea Grason wrote:

I am used to using cachedupdates and the On UpdateRecord event of
TQuery. Now I am switching to ADO. I need to update other tables
when a record is added or edited. When/where is the best place to do
that to ensure that the other tables only get updated if the
insert/edit is successful? In other words I need everything inside
one transaction if possible so that everything updates or nothing at
all. Thanks.

I assume you are using SQL Server. If so, use a trigger to update the
other tables.

--
Bill Todd (TeamB)




.

Relevant Pages