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

    Unanswered: Trigger not capturing info

    Hi,

    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


    return

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

    begin
    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

    BalaMani

  2. #2
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: Trigger not capturing info

    Hi,

    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.

    Richard.

Posting Permissions

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