Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003

    Unanswered: Trigger not capturing info


    I have 2 triggers on a table

    1. create trigger l_service on service for insert,update as
    declare @num_rows integer
    declare @system_timestamp datetime
    DECLARE @person_id varchar(30)

    select @num_rows=@@ROWCOUNT
    if @num_rows=0 return
    select @system_timestamp=getdate(*)
    SELECT @person_id = person_id from ap_user
    UPDATE service_call
    SET last_change_datetime = @system_timestamp,
    last_change_by = @person_id
    FROM service_call, inserted
    WHERE service_call.order_id = inserted.order_id


    2. create trigger
    i_service after insert order 99 on service referencing new as new_name
    for each row

    declare @TempTxID varchar(30);
    declare @AfterDate varchar(32);

    set @DeltaCol = '';

    set @DeltaCol=@DeltaCol || 'order_id,0,';
    set @length=coalesce(byte_length(new_name.order_id),-1);
    set @DeltaCol=@DeltaCol || convert(varchar(50),@length) || ',';
    if (@length > 0) then
    set @DeltaCol=@DeltaCol || new_name.order_id
    end if;
    ..... goes on for e rest of the fields including the above mentioned last_change_by

    Even though the triggers are ordered the second trigger does not capture the updates by the first one. The updates do end up in the table.

    ANy ideas.

    Thanks in advance


  2. #2
    Join Date
    Sep 2002
    Hong Kong

    Re: Trigger not capturing info


    OK I'm confused, so I need to go back to basics.

    1) You create a trigger for insert on service. This trigger updates the table service_call.

    2) Add some rows to the table "service". The trigger for l_service has an update and since service_call doesn't have rows in it yet, the update does nothing when the trigger is fired. If there are rows in service_call, does the second trigger do anything that updates the database? Are those updates hitting their tables? If not check the server configuration option "allow nested trigger".

    sp_configure 'allow nested trigger'

    If it says the Run value is 0, the second trigger is not being executed.

    3) Create the trigger i_service. I'm not sure what this is created on since your post doesn't mention the table. If I assume it's on service_call then this trigger will not be called retrospectively on the existing data - if any exists.

    It looks like your trying to work out the size of the new data, but since I don't where this occurs in the processing, I'm assuming this is a trigger on service_call.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts