1. create trigger l_service on service for insert,update as
declare @num_rows integer
declare @system_timestamp datetime
DECLARE @person_id varchar(30)
if @num_rows=0 return
SELECT @person_id = person_id from ap_user
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 @DeltaCol=@DeltaCol || convert(varchar(50),@length) || ',';
if (@length > 0) then
set @DeltaCol=@DeltaCol || new_name.order_id
..... 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.
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.