hi again, i have a trigger below that works just great everywhere excet the very last part of the code seems to execute once. By this i mean when our DB posts invoices, it hit the trigger and the following is done line by line, as you would expect. However the last UPDATE is only done once and i am not sure why.
I am a bit fresh faced and so it coyuld be due to stupidity or lack of knowledge, so any comments are valued.
I am not sure if its too long, should i cascade some of the sql, because MSSQL is posting a batch of invoices is it catching itself up or does it work through the applications SQL then the trigger, then back to the applications sql ??????????
lots of questions can anyone help
rory
create TRIGGER m3_update_access_fields
ON sl_pl_nl_detail
AFTER UPDATE
AS
declare @DET_PRIM varchar (20)
declare @HEAD_PRIM varchar (20)
declare @newfield varchar (20)
IF @@ROWCOUNT =0
RETURN
BEGIN
/* DETAIL-DIM3 THIS CHECKS FOR THE LAST NUMBER AND SET IT TO @DET_PRIM*/
SELECT @DET_PRIM=MAX(det_primary) FROM sl_pl_nl_detail m
/* DETAIL-DIM3 THIS USES THE DET_PRIM ABOVE TO CREATE A var FOR THE TRANS HEADER*/
SELECT @newfield=det_header_ref FROM sl_pl_nl_detail t
where det_primary = @DET_PRIM
/* HEADER-USER3 THIS GETS THE LAST HEADER RECORD AND SETS IT TO @HEADPRIM*/
SELECT @HEAD_PRIM=MAX(ST_primary) FROM SL_TRANSACTIONS
/* THIS SECTION BELOW VVVVVVV UPDATES THE FIELDS PICKED*/
/* DETAIL-DIM3 THIS UPDATES THE DIM3 FIELD WITH THE HEADER REFIF ITS NOT A tb
TRANS (IE SOURCE NOT 'T')*/
UPDATE sl_pl_nl_detail SET det_dimension3 = @newfield
WHERE det_primary = @DET_PRIM AND DET_SOURCE = 'D'
/* HEADER-USER3 THIS DOES THE SAME IN THE HEADER TABLE !*/
UPDATE SL_TRANSACTIONS SET ST_USER3 = @newfield
WHERE ST_primary = @HEAD_PRIM AND ST_SOURCE = 'D'
/*this section below only deals with items that enter through TB */
/*if the transaction has come from transaction broker it
needs to be copied to user3 in the header line*/
V V V V V V V V V V V V this is the bit that only happens at the end of the batch V V V V V V V V V V V
UPDATE SL_TRANSACTIONS SET ST_USER3 = @newfield
WHERE ST_primary = @HEAD_PRIM AND ST_SOURCE = 'T'
END
I am a bit fresh faced and so it coyuld be due to stupidity or lack of knowledge, so any comments are valued.
I am not sure if its too long, should i cascade some of the sql, because MSSQL is posting a batch of invoices is it catching itself up or does it work through the applications SQL then the trigger, then back to the applications sql ??????????
lots of questions can anyone help
rory
create TRIGGER m3_update_access_fields
ON sl_pl_nl_detail
AFTER UPDATE
AS
declare @DET_PRIM varchar (20)
declare @HEAD_PRIM varchar (20)
declare @newfield varchar (20)
IF @@ROWCOUNT =0
RETURN
BEGIN
/* DETAIL-DIM3 THIS CHECKS FOR THE LAST NUMBER AND SET IT TO @DET_PRIM*/
SELECT @DET_PRIM=MAX(det_primary) FROM sl_pl_nl_detail m
/* DETAIL-DIM3 THIS USES THE DET_PRIM ABOVE TO CREATE A var FOR THE TRANS HEADER*/
SELECT @newfield=det_header_ref FROM sl_pl_nl_detail t
where det_primary = @DET_PRIM
/* HEADER-USER3 THIS GETS THE LAST HEADER RECORD AND SETS IT TO @HEADPRIM*/
SELECT @HEAD_PRIM=MAX(ST_primary) FROM SL_TRANSACTIONS
/* THIS SECTION BELOW VVVVVVV UPDATES THE FIELDS PICKED*/
/* DETAIL-DIM3 THIS UPDATES THE DIM3 FIELD WITH THE HEADER REFIF ITS NOT A tb
TRANS (IE SOURCE NOT 'T')*/
UPDATE sl_pl_nl_detail SET det_dimension3 = @newfield
WHERE det_primary = @DET_PRIM AND DET_SOURCE = 'D'
/* HEADER-USER3 THIS DOES THE SAME IN THE HEADER TABLE !*/
UPDATE SL_TRANSACTIONS SET ST_USER3 = @newfield
WHERE ST_primary = @HEAD_PRIM AND ST_SOURCE = 'D'
/*this section below only deals with items that enter through TB */
/*if the transaction has come from transaction broker it
needs to be copied to user3 in the header line*/
V V V V V V V V V V V V this is the bit that only happens at the end of the batch V V V V V V V V V V V
UPDATE SL_TRANSACTIONS SET ST_USER3 = @newfield
WHERE ST_primary = @HEAD_PRIM AND ST_SOURCE = 'T'
END
