February 27, 2008 at 9:16 pm
I have a table for Work Order Images, into which the HandHeld developer does an RDA.Push of his WO_Images table.
New image records get inserted into SQL table; image records existing in both mobile and SQL table would get updated. (Also, mobile table obviously will not have all images from SQL table.)
Problem is, when a new image is captured on the mobile device, the image_surr_key (primary key) generated for the record would be unique for mobile table, but it may already exist in SQL table.
And, the same key may also be generated on a different mobile device at any time.
On site, when there are going to be multiple syncs, again, the key is bound to repeat in SQL when pushed.
I was wondering whether I could fire a trigger on the table after insert in order to make the keys unique.
Right now, the constraints on the Image_Surr_Key+Created_date fields.
What if I get rid of that and simply have a trigger?
Even if I do have a trigger, how should the update be performed?
This is what my SQL table looks like:
CREATE TABLE WORK_ORDER_IMAGES
(
Image_Surr_Key int NOT NULL,
WO_Hdr_Surr_Key numeric(18, 0) NULL,
Latitude decimal(18, 0) NULL,
Longitude decimal(18, 0) NULL,
Request_Surr_Key numeric(18, 0) NULL,
New_Ad_Surr_Key numeric(18, 0) NULL,
HH_ID numeric(18, 0) NULL
Captured_Image image NULL,
CreatedDatetime datetime NOT NULL,
)
This might be the data present in it at any time:
INSERT INTO tbl_WORK_ORDER_IMAGES
([Image_Surr_Key]
,[WO_Hdr_Surr_Key]
,[CreatedDatetime]
,[Request_Surr_Key]
,[New_Ad_Surr_Key]
,[HH_ID])
SELECT 11
,12
,'02/01/2008'
,146
,NULL
,NULL UNION ALL
SELECT 1
,700
,'02/01/2008'
,146
,NULL
,NULL
FROM ANOTHER DEVICE NOW:
INSERT INTO tbl_WORK_ORDER_IMAGES
([Image_Surr_Key]
,[WO_Hdr_Surr_Key]
,[CreatedDatetime]
,[Request_Surr_Key]
,[New_Ad_Surr_Key]
,[HH_ID])
SELECT 11 --REPEATING
,701
,'02/03/2008'
,146
,NULL
,NULL
I don't know how better to explain this guys, any suggestion would be appreciated.
February 27, 2008 at 9:41 pm
can youc hange your primary key? instead of using some integer value, use the combination of HandheldId + Image_Surr_Key; that way Image_Surr_Key can repeat, but the combination of handheld makes it unique instead of just the one value..
that
Lowell
February 27, 2008 at 10:44 pm
That would be like Device ID + Image_Surr_Key?
That's not going to work either.
Lets take an example:
Today
Device1 pushed 5 images with Surr_Keys 1,2,3,4,5
Device2 pushed 3 images with Surr_Keys 1,2,3.
Fine, no problem, SQL table has 5+3 records and Device ID + Image_Surr_Key would be unique.
However,
Tomorrow Device1 pulls data from SQL, none of the 8 images are required on his device as they probably are images of completed work orders now. Device1 completes a work order and takes 2 images.
Device1 pushes 2 images with Surr_Keys 1,2 (AGAIN)
What happens in SQL? Device1 with Surr_Keys 1,2 repeat!!!!
Could someone tell me if this trigger looks fine....?
CREATE TRIGGER Update_WORK_ORDER_IMAGES
ON tbl_WORK_ORDER_IMAGES
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM INSERTED)
BEGIN
CREATE TABLE #tbl_Row_Num
(
Row_Num INT IDENTITY (1,1),
Image_Surr_Key INT,
WO_Hdr_Surr_Key INT,
Request_Surr_Key INT,
New_Ad_Surr_Key INT,
HH_ID INT,
CreatedDatetime DATETIME
)
IF @@ERROR<>0 GOTO HANDLER
INSERT INTO #tbl_Row_Num
SELECTImage_Surr_Key,
WO_Hdr_Surr_Key,
Request_Surr_Key,
New_Ad_Surr_Key,
HH_ID,
CreatedDatetime
FROM tbl_WORK_ORDER_IMAGES ORDER BY CreatedDatetime ASC
IF @@ERROR<>0 GOTO HANDLER
--TEST
SELECT * FROM #tbl_Row_Num
UPDATE tbl_WORK_ORDER_IMAGES
SET Image_Surr_Key=T.Row_Num
FROM #tbl_Row_Num T
WHERE tbl_WORK_ORDER_IMAGES.Image_Surr_Key=T.Image_Surr_Key
AND ISNULL(tbl_WORK_ORDER_IMAGES.WO_Hdr_Surr_Key,0)=ISNULL(T.WO_Hdr_Surr_Key,0)
AND ISNULL(tbl_WORK_ORDER_IMAGES.Request_Surr_Key,0)=ISNULL(T.Request_Surr_Key,0)
AND ISNULL(tbl_WORK_ORDER_IMAGES.New_Ad_Surr_Key,0)=ISNULL(T.New_Ad_Surr_Key,0)
AND ISNULL(tbl_WORK_ORDER_IMAGES.HH_ID,0)=ISNULL(T.HH_ID,0)
AND tbl_WORK_ORDER_IMAGES.CreatedDatetime=T.CreatedDatetime
AND tbl_WORK_ORDER_IMAGES.Image_Surr_Key<>T.Row_Num
--TEST
DECLARE @Cnt INT
SET @Cnt=@@ROWCOUNT
PRINT @Cnt
IF @@ERROR<>0 GOTO HANDLER
END
RETURN
HANDLER:
RAISERROR('Error inserting into tbl_WORK_ORDER_IMAGES',16,1)
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply