[bali]

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

[richardcrossley]

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.