query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

query help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-07-11 : 17:01:18

[code]

I was not getting the desired output with below trigger.
Please correct the trigger.

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

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources