July 11, 2012 at 3:13 pm
Please help in the below trigger to update the immediate children of parent and children of the immeditae parent not to update all the ancestors in the hierarchy during the insert and update..
Thanks for your help in advance !
CREATE TABLE dbo.MyTable
(
MasterIp varchar(255) NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
,status1 tinyint NOT NULL
,status2 tinyint NOT NULL
,Active_status tinyint NOT NULL
)
GO
CREATE TRIGGER dbo.TR_MyTable_IU
ON dbo.MyTable
AFTER INSERT, UPDATE
AS
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
-- Rows with parents
UPDATE T
SET status2 = COALESCE(P.status2, T.status2)
,Active_status = COALESCE(P.Active_status, T.Active_status)
FROM MyTable T
JOIN inserted I
ON T.MasterIp = I.MasterIp
OUTER APPLY
(
SELECT TOP 1 status2, Active_status
FROM MyTable T1
WHERE T.MasterIp LIKE T1.MasterIp + '.%'
AND T.MasterIp > T1.MasterIp
ORDER BY T1.MasterIp DESC
) P;
-- Rows with Children
UPDATE C
SET status2 = T.status2
,Active_status = T.Active_status
FROM MyTable T
JOIN inserted I
ON T.MasterIp = I.MasterIp
JOIN MyTable C
ON C.MasterIp LIKE T.MasterIp + '.%'
AND C.MasterIp > T.MasterIp;
GO
INSERT INTO dbo.MyTable
VALUES('5.46.200.1.462222.2.1.1.1.1.1', 0, 0, 1);
select * from MyTable;
INSERT INTO dbo.MyTable
VALUES ('5.46.200.1.462222.2.1.1.1.1.1.1', 0, 1, 1);
select * from MyTable;
UPDATE dbo.MyTable
SET status2 = 3
,Active_status = 3
WHERE MasterIp = '5.46.200.1.462222.2.1.1.1.1.1';
select * from MyTable;
Note:when i added the below insert it should not update the values 0,99 for the other masterip since masterip '5.46.200.1.462222.2.1' does not have immediate children.
INSERT INTO dbo.MyTable
VALUES('5.46.200.1.462222.2.1', 0, 9, 9);
select * from MyTable;
Expected output should be:
MasterIp status1 status2 Active_status
------------------------------ ------- ---- -------
5.46.200.1.462222.2.1.1.1.1.1 0 3 3
5.46.200.1.462222.2.1.1.1.1.1.1 0 3 3
5.46.200.1.462222.2.1 0 9 9
drop table MyTable
July 12, 2012 at 3:39 am
Change the part in the code that says -- Rows with Children as shown below
-- Rows with Children
UPDATE C
SET status2 = I.status2
,Active_status = I.Active_status
FROM inserted I
JOIN MyTable C
ON C.MasterIp LIKE I.MasterIp + '.%'
AND CHARINDEX('.', C.MasterIp, LEN(I.MasterIp) + 2 ) = 0
Edit: Removed an additional JOIN
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 12, 2012 at 3:44 pm
Thanks a lot for the reply..I did tried with the mentioned changes and it is updating the grand parent and parent when i tried to modified the parent in other update query.
Please help to correct the query..
CREATE TABLE dbo.MyTable
(
MasterIp varchar(255) NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
,status1 tinyint NOT NULL
,status2 tinyint NOT NULL
,Active_status tinyint NOT NULL
)
GO
CREATE TRIGGER dbo.TR_MyTable_IU
ON dbo.MyTable
AFTER INSERT, UPDATE
AS
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
-- Rows with parents
UPDATE T
SET status2 = COALESCE(P.status2, T.status2)
,Active_status = COALESCE(P.Active_status, T.Active_status)
FROM MyTable T
JOIN inserted I
ON T.MasterIp = I.MasterIp
OUTER APPLY
(
SELECT TOP 1 status2, Active_status
FROM MyTable T1
WHERE T.MasterIp LIKE T1.MasterIp + '.%'
AND T.MasterIp > T1.MasterIp
ORDER BY T1.MasterIp DESC
) P;
-- Rows with Children
UPDATE C
SET status2 = I.status2
,Active_status = I.Active_status
FROM inserted I
JOIN MyTable C
ON C.MasterIp LIKE I.MasterIp + '.%'
AND CHARINDEX('.', C.MasterIp, LEN(I.MasterIp) + 2 ) = 0
GO
INSERT INTO dbo.MyTable
VALUES('5.46.200.1.462222.2.1.1.1.1.1', 0, 0, 1);
select * from MyTable;
INSERT INTO dbo.MyTable
VALUES ('5.46.200.1.462222.2.1.1.1.1.1.1', 0, 1, 1);
select * from MyTable;
UPDATE dbo.MyTable
SET status2 = 3
,Active_status = 3
WHERE MasterIp = '5.46.200.1.462222.2.1.1.1.1.1';
select * from MyTable;
INSERT INTO dbo.MyTable
VALUES('5.46.200.1.462222.2.1', 0, 9, 9);
select * from MyTable;
/* updated to parent again and updated the parent and grand parent also */
UPDATE dbo.MyTable
SET status2 = 1
,Active_status = 3
WHERE MasterIp = '5.46.200.1.462222.2.1.1.1.1.1';
select * from MyTable;
Results after update
MasterIp status1status2Active_status
5.46.200.1.462222.2.1 099
5.46.200.1.462222.2.1.1.1.1.1099
5.46.200.1.462222.2.1.1.1.1.1.1013
Expected Results:
MasterIp status1status2Active_status
5.46.200.1.462222.2.1 09 9
5.46.200.1.462222.2.1.1.1.1.1 01 3
5.46.200.1.462222.2.1.1.1.1.1.1 01 3
drop table MyTable
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply