DROP TABLE HostStatus
GO
DROP TABLE HostStatusListName
GO
CREATE TABLE HostStatusListName (
id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Name nvarchar(50) UNIQUE NOT NULL
)
GO
CREATE TABLE HostStatus (
id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
HostStatusListNameID int NOT NULL REFERENCES HostStatusListName(id),
/* Hostname nvarchar(50) REFERENCES Host,
ProductName nvarchar(50) REFERENCES ProductFamily,
Environment nvarchar(50) REFERENCES Environment, */
HostID int unique REFERENCES Host,
ProductID int REFERENCES ProductFamily,
EnvironmentID int REFERENCES Environment,
IsInActiveDirectory bit NOT NULL,
IsInInventoryDB bit NOT NULL,
ManagementIP nvarchar(50),
ProductionIP nvarchar(50),
Reachable bit NOT NULL,
ReachableDate datetime NOT NULL DEFAULT getdate(),
ServerModel nvarchar(50)
)
DROP VIEW vHostStatus
GO
CREATE VIEW vHostStatus AS
SELECT hs.id, Name, Hostname, ProductName, Environment, IsInActiveDirectory, IsInInventoryDB,
ManagementIP, ProductionIP, Reachable, ReachableDate, ServerModel FROM HostStatus hs
LEFT OUTER JOIN HostStatusListName hsln ON hs.HostStatusListNameId = hsln.id
LEFT OUTER JOIN Host h ON hs.HostId = h.Machineid
LEFT OUTER JOIN ProductFamily pf ON hs.ProductID = pf.ProductID
LEFT OUTER JOIN Environment e ON hs.EnvironmentID = e.EnvironmentID
GO
DROP TRIGGER tr_vHostStatus_IO_U
GO
CREATE TRIGGER tr_vHostStatus_IO_U ON vHostStatus INSTEAD OF UPDATE AS
UPDATE HostStatus SET
HostStatusListNameid=(SELECT TOP 1 id FROM HostStatusListName hsln WHERE hsln.Name=i.Name),
Productid=(SELECT top 1 productid FROM ProductFamily pf where pf.ProductName=i.ProductName),
Hostid = ( SELECT TOP 1 Machineid FROM Host h WHERE h.Hostname=i.Hostname ),
Environmentid = ( SELECT TOP 1 Environmentid FROM Environment e WHERE e.Environment=i.Environment)
FROM inserted i
INNER JOIN HostStatus h on h.id=i.id
GO
DROP TRIGGER tr_vHostStatus_IO_I
GO
CREATE TRIGGER tr_vHostStatus_IO_I ON vHostStatus INSTEAD OF INSERT AS
DECLARE @nameid as int
DECLARE @productid as int
DECLARE @machineid as int
DECLARE @environmentid as int
DECLARE @IsInActiveDirectory as bit
DECLARE @IsInInventoryDB as bit
DECLARE @ManagementIP as nvarchar(50)
DECLARE @ProductionIP as nvarchar(50)
DECLARE @Reachable as bit
DECLARE @ReachableDate as datetime
DECLARE @ServerModel as nvarchar(50)
select top 1 @nameid=hsln.id FROM HostStatusListName hsln INNER JOIN inserted i ON hsln.Name=i.Name
select top 1 @productid = productid FROM ProductFamily pf INNER JOIN inserted i on pf.ProductName=i.ProductName
select top 1 @machineid= machineid FROM Host h INNER JOIN inserted i on h.Hostname=i.Hostname
select top 1 @environmentid = @environmentid FROM Environment e INNER JOIN inserted i on e.Environment=i.Environment
select @IsInActiveDirectory=IsInActiveDirectory, @IsInInventoryDB =IsInInventoryDB ,
@ManagementIP = ManagementIP, @ProductionIP = ProductionIP, @Reachable=Reachable,
@ReachableDate = ReachableDate, @ServerModel=ServerModel FROM inserted
INSERT INTO HostStatus (
HostStatusListNameid, Productid,
Hostid, Environmentid,
IsInActiveDirectory, IsInInventoryDB, ManagementIP, ProductionIP,
Reachable, ReachableDate, ServerModel )
VALUES ( @nameid, @productid,
@machineid, @environmentid,
@IsInActiveDirectory, @IsInInventoryDB, @ManagementIP, @ProductionIP,
@Reachable, @ReachableDate, @ServerModel
)
GO
INSERT INTO HostStatusListName (Name) VALUES (‘Etime’)
INSERT INTO HostStatusListName (Name) VALUES (‘Ehrms-unix’)
INSERT INTO HostStatusListName (Name) VALUES (‘Ehrms-windows’)
Insert INTO vHostStatus (id, Name,Hostname,ProductName, Environment, IsInActiveDirectory, IsInInventoryDB, ManagementIP,ProductionIP,Reachable,ReachableDate, ServerModel)
VALUES (1,’Etime’,’test’,’Etime’,’P’,0,0,’172.30.1.1′,’172.30.1.1′,1,getdate(),’some server’ )
SELECT * from vHostStatus
SELECT * from HostStatusListName