trigger que

[habesha]

All,

I am confused what to do

I have a parent table and more than 40 child tables for this table.
I am tracking a change on these table,

If a change is done on one of the tables either it is the parent or a child, I will update a column in the parent table
Here is what I did, I defined a trigger on each table.
when, when a change is made on the parent, the trigger will be fire and update a date column, that is fine with me.
but the problem is this, when a child table is updated, a trigger on the child will be fired and update a date column in the parent table, and this fires the trigger defined on the parent table and update the column twice. I don't want this to happen. I want the triggers on the child tables update the date column on the parent table, but the trigger on the parent table is not firing because of that

Thanks

 

[mrdenny]

You need to define your triggers on the child tables to only update the parent if a column other than the one that the parent table updates is updated.

Look at the UPDATE() function.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[habesha]

Thank you for ur response,

here is the problem, all triggers are updating the same column the DateLastUpdated column in the parent table

thanks

 

[mrdenny]

That's fine. You just need to update the trigger to that if that's the only column that is updated the code to update the other tables doesn't run.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[habesha]

Thanks again

let's say I am using the update() function in the trigger defined on the parent table. like this
...trigger
if not (upate(dateLastUpdated))
begin
update parenttable
set dateLastUpdated = getdate()
from parenttable a inner join inserted b
on a.id = b.id
end

let's say I try to update the child table the trigger on the child table fires and updates the dateLastUpdated column on the parent table then this fires the trigger on the parent table, it comes and checks whether the column is updated, it is updated , so it doesn't execute the trigger. that is cool.
what if the parent table got updated after child table is updated , the trigger fires and checks whether the column is updated, it is( it was already updated by the child trigger) there fore the trigger won't execute.
but it should be executed
what is ur suggestion. i don't know for how long the update() function is retaining the true for updated columns

Thanks

 

[mrdenny]

The update() function will return true is the column is updated within that transaction only.

You would want to check some other columns as well and if any of them are updated the trigger fires (especially if you happen to have a column or two which would be updated every time by the client).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com