SQL Server Performance

trigger question

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by habesha, Dec 19, 2006.

  1. habesha New Member

    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 on it, 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
  2. ranjitjain New Member

    Hi,
    I think trigger is not the right option over here,
    Any change in parent table will be done through procedures right? so in those procedures itself you can check whether any rows have been modified or not, if yes then finally update the parent table column, Thats it.

    The same should be followed for child tables.
    What do you say?
  3. habesha New Member

    how can the proc track a change made on those table )the parent as well as the 40 child tables)

    Thanks,
  4. ranjitjain New Member

    Hi,
    When you say "change made on table", what kind of change you are pointing to.
    Is it data modification or schema change, please clarify?

    If we consider data modifications then
    Lets consider you have a procedure which will update a record in parent table and then 40 child tables.

    alter procedure proc_name(few parameters)
    as

    update parenttable set some values
    --check for rowcount
    IF @@rowcount>0 print 'need to update my column' and set a flag to 1

    if flag is set then no need to check for rowcount for child tables else

    Same way you need to check for 40 child tables if in between flag is set, no need to set it again.

    Finally at the end of procedure if flag is true then update your column in parent table else return

Share This Page