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-02 : 15:05:32

[code]
I need a trigger on insert when a new child masterip is created set the values of the child status1,status2 as parent values

Example for insert trigger:

MasterIp status1 status2
----------------------------- ------- --------
5.46.200.1.462222.2.1.1.1.1.1 1 0
5.46.200.1.462222.2.1.1.1.1.1.1 1 0
5.46.200.1.462222.2.1.1.1.1.1.2 1 0
5.46.200.1.462222.2.1.1.1.1.5 1 1
5.46.200.1.462222.2.1.1.1.1.5.1 1 1
5.46.200.1.462222.2.1.1.1.1.6 1 0
5.46.200.1.462222.2.1.1.1.1.6.1 1 0

Ex for Update trigger:

If parent record is modified then update the child values of the status1,status2 as parent the values

MasterIp status1 status2
----------------------------- ------- --------
5.46.200.1.462222.2.1.1.1.1.1 0 1
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.2 1 0
5.46.200.1.462222.2.1.1.1.1.5 1 0
5.46.200.1.462222.2.1.1.1.1.5.1 1 0
5.46.200.1.462222.2.1.1.1.1.6 1 1
5.46.200.1.462222.2.1.1.1.1.6.1 1 0

Please let me know if i m not clear in my explanation

Thanks for your help in advance
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-02 : 19:27:07

something like


CREATE TRIGGER UPdateChildStatus_Trg
ON YourTable
FOR INSERT 
AS
BEGIN
UPDATE t
SET t.status1= t1.status1,
t.status2 =t2.status2
FROM table t
INNER JOIN INSERTED i
ON i.PK = t.PK
CROSS APPLY (SELECT TOP 1 *
             FROM Table
             WHERE t.MasterIp LIKE MasterIp + '%'
             AND t.MasterIp <> MasterIp
             ORDER BY LEN(MasterIp) DESC)t1                                               
END


similarly for update

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-07-03 : 11:42:33

Thanks Visakh !

Please correct me if i am wrong update trigger !


CREATE TRIGGER UPdateChildStatuswhenMasterchanged_Trg
ON YourTable
FOR UPDATE 
AS
BEGIN
UPDATE t
SET t.status1= t1.status1,
t.status2 =t2.status2
FROM table t
INNER JOIN INSERTED i
ON i.PK = t.PK
CROSS APPLY (SELECT TOP 1 *
             FROM Table
             WHERE t.MasterIp LIKE MasterIp + '%'
             AND t.MasterIp <> MasterIp
             ORDER BY LEN(MasterIp) DESC)t1                                               
END

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-07-05 : 11:16:07

Below update trigger is not working as expected please correct me..

CREATE TRIGGER UPdateChildStatuswhenMasterchanged_Trg
ON YourTable
FOR UPDATE
AS
BEGIN
UPDATE t
SET t.status1= t1.status1,
t.status2 =t2.status2
FROM table t
INNER JOIN INSERTED i
ON i.PK = t.PK
CROSS APPLY (SELECT TOP 1 *
FROM Table
WHERE t.MasterIp LIKE MasterIp + '%'
AND t.MasterIp <> MasterIp
ORDER BY LEN(MasterIp) DESC)t1

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-07-05 : 11:33:35

Your question is unhelpful. What is the error thrown? What are the unexpected results?

Do you REALLY have a column named PK?

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 11:34:32

did you replace PK with your actual primay key?
also it would be great if you can specify what error you're getting rather than just telling its not working

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-07-05 : 12:13:20

I have replaced the pkey with primary key column and the values are not getting updated as expected when the values of the master record is modified for status1 and status2..it doesn't gave me any error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources