July 9, 2012 at 4:39 pm
I need a trigger on insert
Example for insert trigger:
MasterIp studentstat stustatus2 Active_status
----------------------------- ---------- -------- -------------
5.46.200.1.462222.2.1.1.1.1.1 0 0 1
If i created a record with "5.46.200.1.462222.2.1.1.1.1.1.1" then i need the stustatus2,Active_status values inherited from parent i.e "5.46.200.1.462222.2.1.1.1.1.1"
INSERT INTO MyTable (MasterIp , status1,status2,Active_status)
VALUES ('5.46.200.1.462222.2.1.1.1.1.1.1',0,1,1);
Expected output should be:
MasterIp studentstat stustatus2 Active_status
----------------------------- ---------- -------- -------------
5.46.200.1.462222.2.1.1.1.1.1 1 0 1
5.46.200.1.462222.2.1.1.1.1.1.1 0 0 1
I need a trigger for update as well.
If the updated MasterIp has childrens, set studentstat = 1 and all the children of that MasterIp should be set to the same stustatus2 and Active_status values as the parent MasterIp.
Ex: for Update trigger:
MasterIp studentstat stustatus2 Active_status
----------------------------- ------- -------- -------------
5.46.200.1.462222.2.1.1.1.1.1 0 0 1
5.46.200.1.462222.2.1.1.1.1.1.1 0 0 0
update MyTable set stustatus2=1 where masterip ='5.46.200.1.462222.2.1.1.1.1.1'
Expected output:
MasterIp studentstat stustatus2 Active_status
----------------------------- ------- -------- -------------
5.46.200.1.462222.2.1.1.1.1.1 1 1 1
5.46.200.1.462222.2.1.1.1.1.1.1 0 1 1
Please let me know if i m not clear in my explanation
Thanks for your help in advance
July 9, 2012 at 6:17 pm
How do you decide it's a parent? If there's any match of a substring?
What if you add this row next: 5.46.200.1.462222.2.1.1.1.1.1.1.2
Are there two parents?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 9, 2012 at 7:51 pm
5.46.200.1.462222.2.1.1.1.1.1.1.2 will be children of parent 5.46.200.1.462222.2.1.1.1.1.1.1
Yes If there's any match of a substring then it would be parent.chop off the right most after period then it would be parent.
I am looking for immediate parent not the grand parent.
Ex:
MasterIp studentstat stustatus2 Active_status
----------------------------- ---------- -------- -------------
5.46.200.1.462222.2.1.1.1.1.1 1 0 1 (Parent)
5.46.200.1.462222.2.1.1.1.1.1.1 0 0 1 (child)
5.46.200.1.462222.2.1.1.1.1.1.2 0 0 1 (Child)
5.46.200.1.462222.2.1.1.1.1.1.3 0 0 1 (Parent)
5.46.200.1.462222.2.1.1.1.1.1.1 0 0 1 (parent)
5.46.200.1.462222.2.1.1.1.1.1.1.1 0 0 1 (child)
5.46.200.1.462222.2.1.1.1.1.1.1.2 0 0 1 (Child)
5.46.200.1.462222.2.1.1.1.1.1.1.3 0 0 1 (Child)
Please let me know i m not clear in my explanation.
July 11, 2012 at 6:07 am
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 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;
INSERT INTO dbo.MyTable
VALUES('5.46.200.1.462222.2.1', 0, 9, 9);
select * from MyTable;
INSERT INTO dbo.MyTable
VALUES('5.46.200.1.462222.9.1.1.1.1.1', 0, 0, 1);
select * from MyTable;
DROP TABLE dbo.MyTable
July 11, 2012 at 10:20 am
Thanks a lot for the solution..
I want to update the parent and immediate child only.(Immediate parent for a child can be determine by chopping of the right most periodplusvalue)
Example:
5.46.200.1.462222.2.1 parent
5.46.200.1.462222.2.1.1 child
In the scripts when i ran it is updating the grand children
INSERT INTO dbo.MyTable
VALUES('5.46.200.1.462222.2.1', 0, 9, 9);
select * from MyTable;
MasterIp status1 status2Active_status
5.46.200.1.462222.2.1 0 9 9
5.46.200.1.462222.2.1.1.1.1.10 9 9
5.46.200.1.462222.2.1.1.1.1.1.10 9 9
I want the output should be because it doesn't have the immediate children.
MasterIpstatus1status2Active_status
5.46.200.1.462222.2.1 099
5.46.200.1.462222.2.1.1.1.1.1033
5.46.200.1.462222.2.1.1.1.1.1.1033
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply