|
|
| 説明 |
if exists (select * from sysobjects where id = object_id(N'[dbo].[tblCluster]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCluster]
GO
CREATE TABLE [dbo].[tblCluster] (
[nClusterID] [int] IDENTITY (1, 1) NOT NULL ,
[szClusterCaption] [varchar] (255) NOT NULL ,
[szClusterInformation] [text] NULL ,
[szClusterQuery] [text] NULL ,
[dClusterCreated] [datetime] NOT NULL ,
[dClusterUpdated] [datetime] NOT NULL ,
[bClusterDeleted] [bit] NOT NULL ,
[nTableID] [int] NOT NULL ,
[nKnowledgeID] [char] (10) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TRIGGER OnInsertCluster ON tblCluster
FOR INSERT
AS
INSERT INTO tblKnowledge (nKnowledgeExtID, nTableID, szKnowledgeCaption)
SELECT nClusterID,10, szClusterCaption FROM inserted
UPDATE tblCluster
SET nKnowledgeID=
(SELECT nKnowledgeID
FROM tblKnowledge
WHERE nKnowledgeExtID = inserted.nClusterID AND nTableID = 10
)
FROM inserted
WHERE tblCluster.nClusterID = inserted.nClusterID
--------------------------------------
CREATE TRIGGER OnUpdateCluster ON tblCluster
FOR UPDATE
AS
UPDATE tblKnowledge
SET
nKnowledgeExtID = inserted.nClusterID,
nTableID = 10,
szKnowledgeCaption = inserted.szClusterCaption,
dKnowledgeUpdated = getdate(),
szKnowledgeLinkedURL = '/UnivInfo/Cluster.asp?nClusterID=' + CONVERT(varchar(12), inserted.nClusterID)
FROM inserted
WHERE tblKnowledge.nKnowledgeID = inserted.nKnowledgeID AND tblKnowledge.nTableID = 10
|
|