August 23, 2007 at 11:45 am
I am kinda a new at this and need some help. I hope this is a simple question. I know how to update a single table, but now I want to update two tables at the same time and am unsure how to proceed. Here is my code....
UPDATE dbo.CHECK_HDR, CHECK_LINE
SET dbo.CHECK_HDR.DEPART = '0000',
dbo.CHECK_LINE.DEPART = '0000'
FROM dbo.CHECK_HDR INNER JOIN
dbo.CHECK_LINE ON dbo.CHECK_HDR.DOC_NO = dbo.CHECK_LINE.DOC_NO
WHERE (dbo.CHECK_HDR.DOC_TYPE = 'AR') AND
(dbo.CHECK_HDR.DOC_NO = @DocNo) AND
(dbo.CHECK_LINE.LINE = @Line)
Thank you,
Wayne
Wayne Benhart
August 23, 2007 at 12:02 pm
you'll need to update each table spearately, but most likely in a transaction, so it is update both or nothing;
SET XACT_ABORT ON --any error rollsback the whole thing...IF a transaction was created:
BEGIN TRAN
--first update
UPDATE dbo.CHECK_HDR
SET dbo.CHECK_HDR.DEPART = '0000'
FROM dbo.CHECK_LINE --only the other table
WHERE dbo.CHECK_HDR.DOC_NO = dbo.CHECK_LINE.DOC_NO
AND dbo.CHECK_HDR.DOC_TYPE = 'AR'
AND dbo.CHECK_HDR.DOC_NO = @DocNo
AND dbo.CHECK_LINE.LINE = @Line
--second update
UPDATE dbo.CHECK_LINE
SET dbo.CHECK_LINE.DEPART = '0000'
FROM dbo.CHECK_HDR --only the other table
WHERE dbo.CHECK_HDR.DOC_NO = dbo.CHECK_LINE.DOC_NO
AND dbo.CHECK_HDR.DOC_TYPE = 'AR'
AND dbo.CHECK_HDR.DOC_NO = @DocNo
AND dbo.CHECK_LINE.LINE = @Line
COMMIT TRAN
Lowell
August 24, 2007 at 5:26 am
Ok, I've tried what you posted, but it still didn't work. If I use either one but not both of the updated statements, it will work. Here is my code;
CREATE TRIGGER [dbo.CHECK_LINE_DEPT_Ins] ON [dbo].[CHECK_LINE] FOR INSERT NOT FOR REPLICATION AS
BEGIN
SET NOCOUNT ON
DECLARE @DocNO INT,
@Line INT,
@TYPE VARCHAR,
@RowsAffected INT
SELECT @RowsAffected = COUNT(*) FROM INSERTED
IF( @RowsAffected > 0)
BEGIN -- Update division when a part is added
SELECT
@DocNO = [DOC_NO],
@Line = [LINE],
@TYPE = [DOC_TYPE]
FROM INSERTED
IF( @TYPE = 'AR')
BEGIN
SET XACT_ABORT ON --any error rollsback the whole thing...IF a transaction was created:
BEGIN TRAN
UPDATE dbo.CHECK_LINE
SET dbo.CHECK_LINE.DEPART = '0000'
WHERE (dbo.CHECK_LINE.DOC_TYPE = @TYPE) AND
(dbo.CHECK_LINE.DOC_NO = @DocNo) AND
(dbo.CHECK_LINE.LINE = @Line)
UPDATE dbo.CHECK_HDR
SET dbo.CHECK_HDR.DEPART = '0000'
WHERE (dbo.CHECK_HDR.DOC_TYPE = @TYPE) AND
(dbo.CHECK_HDR.DOC_NO = @DocNo)
COMMIT TRAN
END -- IF( @TYPE = 'AR')
END -- IF( @RowsAffected > 0 )
END
GO
Wayne Benhart
August 24, 2007 at 5:30 am
oops it actually does work, my code wasn't completely right.
I need to declare one of my variables correctly.
DECLARE @DocNO INT,
@Line INT,
@TYPE VARCHAR, <--- @TYPE VARCHAR(2),
@RowsAffected INT
Thank you very much for your help.
Wayne Benhart
August 24, 2007 at 6:35 am
Your code is also flawed!
What will do you when more than one record is updated at a time?
CREATE TRIGGER [dbo.CHECK_LINE_DEPT_Ins] ON [dbo].[CHECK_LINE]
FOR INSERT NOT FOR REPLICATION
AS
BEGIN
SET NOCOUNT ON
IF EXISTS (SELECT * FROM INSERTED AS i WHERE i.DOC_TYPE = 'AR')
BEGIN
SET XACT_ABORT ON
BEGIN TRAN
UPDATE cl
SET cl.DEPART = '0000'
FROM dbo.CHECK_LINE AS cl
INNER JOIN INSERTED AS i ON i.DOC_TYPE = cl.DOC_TYPE
AND i.DOC_NO = cl.DOC_NO
AND i.LINE = cl.LINE
WHERE i.DOC_TYPE = 'AR'
UPDATE hdr
SET hdr.DEPART = '0000'
FROM dbo.CHECK_HDR AS hdr
INNER JOIN INSERTED AS i ON i.DOC_TYPE = cl.DOC_TYPE
AND i.DOC_NO = cl.DOC_NO
WHERE i.DOC_TYPE = 'AR'
COMMIT TRAN
END
END
N 56°04'39.16"
E 12°55'05.25"
August 25, 2007 at 7:44 am
Thank you for your help,
Actually the Check_Hdr.Doc_No is unique and the Check_Line.Line is Unique. The direct link between the tables in the Doc_No field. In testing the code it works, it may not be the cleanest way to get-er done but it works.
Thank you for the insight for better techniques in my SQL mess of code.
Wayne
Wayne Benhart
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply