Sync information

[IT_VVD]

On a computer asset, I entered information in the building and department section. Is it possible that this information is dynamically updated with the connected assets (relations) like monitors?

I need to create a report every year on all equipements in each building. It will be very usefull to track equipements that can be move without IT intervention.

Thanks!

[prighi61]

use [lansweeperdb]
go

drop trigger dbo.UpdateChildAsset
go

CREATE TRIGGER dbo.UpdateChildAsset
ON tblAssetCustom
AFTER UPDATE As

BEGIN
SET NOCOUNT ON;

DECLARE @Building nvarchar(max),
@Department nvarchar(max),
@ParentAssetID int;

SELECT @Building = Building, @Department = Department FROM inserted;
SELECT @ParentAssetID = AssetID FROM inserted;

UPDATE tblAssetCustom SET Building = @Building, Department = @Department where tblAssetCustom.AssetID in
(Select tblAssetRelations.ChildAssetID from tblAssetRelations
WHERE tblAssetRelations.ParentAssetID = @ParentAssetID
)
END

drop trigger dbo.UpdateAssetFromParent
go

CREATE TRIGGER dbo.UpdateAssetFromParent
ON tblAssetRelations
AFTER INSERT As

BEGIN
SET NOCOUNT ON;

DECLARE @Building nvarchar(max),
@Department nvarchar(max);
DECLARE @ChildAssetID int;

SELECT Top 1 @Building = Building, @Department = Department FROM inserted, tblAssetCustom where tblAssetCustom.AssetID = inserted.ParentAssetID;
SELECT @ChildAssetID = ChildAssetID FROM inserted;

UPDATE tblAssetCustom SET Building = @Building, Department = @Department where tblAssetCustom.AssetID = @ChildAssetID;

END

[prighi61]

The first trigger could be:

use [lansweeperdb]
go

-- drop trigger dbo.UpdateChildAsset
-- go

CREATE TRIGGER dbo.UpdateChildAsset
ON tblAssetCustom
AFTER UPDATE As

BEGIN
SET NOCOUNT ON;

DECLARE @Building nvarchar(max);
DECLARE @ParentAssetID int;

SELECT @Building = Building FROM inserted;
SELECT @ParentAssetID = AssetID FROM inserted;

UPDATE tblAssetCustom SET Building = @Building where tblAssetCustom.AssetID in
(Select tblAssetRelations.ChildAssetID from tblAssetRelations
WHERE tblAssetRelations.ParentAssetID = @ParentAssetID
)
END

[prighi61]

I think you should create a database trigger that traps tblAssets changes and tsysAssetRelationTypes additions so to keep child assets updated. Be aware that a child asset could have more than one parent, maybe you should filter on the Relation Type field.