April 10, 2008 at 7:59 am
Hi
The below trigger works with one record.
When i do for multiple records transfer to SVC00700 & SVC00701, it is not updating properly.
SVC00700 is Destination table. (link field ORDDOCID for SVC00701)
SVC00701 is child table of SVC00700 (link field ORDDOCID for SVC00700) (link field CALLNBR for SVC00200)
SVC00200 is Source table for Address to pickup. (link field CALLNBR).
Can some body help me to rectify the trigger to work.
ALTER TRIGGER [dbo].[tr_SVC_SVC00701_InsertAddress] ON [dbo].[SVC00701]
FOR INSERT AS
SELECT * FROM inserted
WHERE
CALLNBR <> ''
IF @@rowcount = 1
BEGIN
update SVC00700 set ADDRESS1 = isnull(A.ADDRESS1,''), ADDRESS2 = isnull(A.ADDRESS2,''), CITY = isnull(A.CITY,'')
FROM dbo.SVC00200 A RIGHT OUTER JOIN
inserted B ON A.CALLNBR = B.CALLNBR RIGHT OUTER JOIN
dbo.SVC00700 C ON B.ORDDOCID = C.ORDDOCID
END
Thx
Vijji
April 10, 2008 at 8:13 am
The reason it's only working for one row is because whoever designed the trigger decided to design it to only work with one row (as long as CALLNBR is not blank)
if @@rowcount = 1 --this means the update statement will only ever work if one row was inserted
April 10, 2008 at 8:21 am
Hi Karl
after i remove IF @@rowcount = 1, it is updating only for the last record, remaining records are blank.
Thx
Regards
vijji
April 10, 2008 at 8:28 am
Ouch!
RIGHT JOIN?
N 56°04'39.16"
E 12°55'05.25"
April 10, 2008 at 8:33 am
Try this rewrite
ALTER TRIGGER dbo.tr_SVC_SVC00701_InsertAddress ON dbo.SVC00701
FOR INSERT
AS
UPDATE j
SET j.Address1 = ISNULL(q.Address1, ''),
j.Address2 = ISNULL(q.Address2, ''),
j.City = ISNULL(q.City, '')
FROM dbo.SVC00700 AS j
INNER JOIN inserted AS i ON i.OrdDocID = j.OrdDocID
INNER JOIN dbo.SVC00200 AS q ON q.CallNbr = i.CallNbr
WHERE i.CALLNBR > ''
N 56°04'39.16"
E 12°55'05.25"
April 10, 2008 at 8:35 am
Vijji (4/10/2008)
Hi Karlafter i remove IF @@rowcount = 1, it is updating only for the last record, remaining records are blank.
Thx
Regards
vijji
That's because you're doing a right outer join and the records are null, which you're converting to blanks using your isnull statement. I assumed that's what you wanted.
April 10, 2008 at 3:13 pm
THX Peso & TO ALL
THE BELOW SCRIPT IS WORKING FINE.
ALTER TRIGGER dbo.tr_SVC_SVC00701_InsertAddress ON dbo.SVC00701
FOR INSERT
AS
UPDATE j
SET j.Address1 = ISNULL(q.Address1, ''),
j.Address2 = ISNULL(q.Address2, ''),
j.City = ISNULL(q.City, '')
FROM dbo.SVC00700 AS j
INNER JOIN inserted AS i ON i.OrdDocID = j.OrdDocID
INNER JOIN dbo.SVC00200 AS q ON q.CallNbr = i.CallNbr
WHERE i.CALLNBR <> ''
Thx
Vijji
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply