ナレッジ-tblComputer

ALTER TABLE [dbo].[tblTableContent_1999FEB] DROP CONSTRAINT FK___5__13
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[tblComputer_1999JAN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblComputer_1999JAN]
GO

CREATE TABLE [dbo].[tblComputer_1999JAN] (
[nComputerID] [int] IDENTITY (1, 1) NOT NULL ,
[szComputerName] [varchar] (255) NOT NULL ,
[szComputerCaption] [varchar] (255) NOT NULL ,
[pszComputerContent] [text] NULL ,
[dComputerEstablish] [datetime] NOT NULL ,
[dComputerAbandonment] [datetime] NOT NULL ,
[dComputerPurchase] [datetime] NOT NULL ,
[nEmployeeID] [int] NOT NULL ,
[nComputerOrnerID] [int] NOT NULL ,
[szComputerDealerName] [varchar] (255) NULL ,
[szComputerWorkGroup] [varchar] (16) NULL ,
[nLocationID] [int] NOT NULL ,
[szComputerWorkingPlace] [varchar] (255) NULL ,
[szComputerNumber] [varchar] (255) NULL ,
[dComputerUpdated] [datetime] NOT NULL ,
[dComputerRegisted] [datetime] NOT NULL ,
[bComputerDeleted] [tinyint] NOT NULL ,
[bComputerRetired] [tinyint] NOT NULL ,
[bComputerOfficial] [tinyint] NOT NULL ,
[nComputerPrevID] [int] NULL ,
[nKnowledgeID] [int] NOT NULL
)
GO

----------------------------------------------------
CREATE TRIGGER OnInsertComputer ON tblComputer_1999JAN
FOR INSERT
AS
INSERT INTO tblKnowledge (nKnowledgeExtID, nTableID, szKnowledgeCaption)
SELECT nComputerID,13, szComputerName FROM inserted

UPDATE tblComputer_1999JAN
SET nKnowledgeID=
(SELECT nKnowledgeID
FROM tblKnowledge
WHERE nKnowledgeExtID = inserted.nComputerID AND nTableID = 13
)
FROM inserted
WHERE tblComputer_1999JAN.nComputerID = inserted.nComputerID
-------------------------------------------------------------
CREATE TRIGGER OnUpdateComputer ON tblComputer_1999JAN
FOR UPDATE
AS
UPDATE tblKnowledge
SET
nKnowledgeExtID = inserted.nComputerID,
nTableID = 13,
szKnowledgeCaption = inserted.szComputerName,
dKnowledgeUpdated = getdate(),
szKnowledgeLinkedURL = '/UnivInfo/Computer.asp?nComputerID=' + CONVERT(varchar(12), inserted.nComputerID)
FROM inserted

WHERE tblKnowledge.nKnowledgeID = inserted.nKnowledgeID AND tblKnowledge.nTableID = 13