Sql-Server
如果每個項目的列 <>“文本”,則插入表格
如果將新行插入到暗表中,我創建了兩個觸發器以插入到事實表中。檢查項目是否已經存在,如果不存在,則將觸發查詢並為每個項目添加 12 個新行。我已經對此進行了測試並且工作正常。現在我需要另一個觸發器進行插入檢查檢查項目是否存在,然後不要觸發,但此外,此插入查詢需要檢查每個項目的 Periodcheck 是否等於“Later”,如果這樣的行不存在存在,那麼它應該在我的事實表中添加一個新行。嘗試了一些不同的東西,但似乎無法在不為項目和 Periodcheck 創建重複行的情況下將它們放在一起。
dimtable 表看起來像這樣(假設剛剛添加了項目 2):
像這樣的事實,其中項目 1 是正確的,但項目 2 缺少 Periodcheck = ‘Later’ 的最後一行:
下面的這個查詢應該只為項目 2 添加一個 Periodcheck = ‘Later’ 的行,因為該行在我的事實表中不存在。
CREATE TRIGGER project_insert ON dimtable AFTER INSERT AS BEGIN Insert into facttable (ProjectID, Status, Period, Periodcheck) SELECT ProjectID , Status , Period = '205001' , Periodcheck = 'Later' FROM dimtable d WHERE EXISTS(Select ProjectID FROM facttable f WHERE d.ProjectID = f.ProjectID) and NOT EXISTS(SELECT Periodcheck FROM facttable f WHERE t2.Periodcheck = 'Later') END
最終目標是將此行添加到我的事實表中還沒有此行的項目中。有關如何更改查詢以執行此操作的任何建議?它執行,但我已經通過將項目添加到 dimtable 進行了測試,並且此觸發器沒有添加我想要的行。
非常感謝!
您可以在一個觸發器中完成所需的一切。
例如,給定表格:
CREATE TABLE dbo.Projects ( ProjectID integer NOT NULL, [Status] character(1) NOT NULL, CONSTRAINT [PK dbo.Projects ProjectID] PRIMARY KEY CLUSTERED (ProjectID), CONSTRAINT [UQ dbo.Projects ProjectID, Status] UNIQUE NONCLUSTERED (ProjectID, [Status]), CONSTRAINT [CK dbo.Projects Status] CHECK ([Status] IN ('A')) ); CREATE TABLE dbo.ProjectPeriods ( ProjectID integer NOT NULL, [Status] character(1) NOT NULL, [Period] character(6) NOT NULL, PeriodCheck varchar(5) NULL, CONSTRAINT [PK dbo.ProjectPeriods ProjectID, Period] PRIMARY KEY CLUSTERED (ProjectID, [Period]), CONSTRAINT [FK dbo.Projects ProjectID, Status] FOREIGN KEY (ProjectID, [Status]) REFERENCES dbo.Projects (ProjectID, [Status]), CONSTRAINT [CK dbo.Projects Period] CHECK ([Period] NOT LIKE '^[0-9]'), CONSTRAINT [CK dbo.Projects PeriodCheck] CHECK (PeriodCheck = 'Later') );
以下觸發器會創建十二個句點和“稍後”條目(如果它們缺失)。它還會刪除任何不應該存在的條目。
CREATE TRIGGER [dbo.Projects AI] ON dbo.Projects AFTER INSERT AS BEGIN IF @@ROWCOUNT = 0 RETURN; SET NOCOUNT ON; MERGE dbo.ProjectPeriods AS PP USING ( SELECT I.ProjectID, I.[Status], [Period] = ISNULL(CONVERT(character(6), DATEADD(MONTH, Months.n, CURRENT_TIMESTAMP), 112), '205001'), PeriodCheck = CASE WHEN Months.n IS NULL THEN 'Later' ELSE NULL END FROM Inserted AS I CROSS JOIN ( VALUES (01), (02), (03), (04), (05), (06), (07), (08), (09), (10), (11), (12), (NULL) ) AS Months (n) ) AS PD ON PD.ProjectID = PP.ProjectID AND PD.[Period] = PP.[Period] WHEN NOT MATCHED BY TARGET THEN INSERT (ProjectID, [Status], [Period], PeriodCheck) VALUES (PD.ProjectID, PD.[Status], PD.[Period], PD.PeriodCheck) WHEN NOT MATCHED BY SOURCE AND PP.ProjectID IN (SELECT I.ProjectID FROM Inserted AS I) THEN DELETE; END;
執行:
INSERT dbo.Projects (ProjectID, [Status]) VALUES (1, 'A'); INSERT dbo.Projects (ProjectID, [Status]) VALUES (2, 'A');
或等價物:
INSERT dbo.Projects (ProjectID, [Status]) VALUES (1, 'A'), (2, 'A');
產生所需的結果:
線上展示:https ://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8f34f1d9eb00aa1b8603498860a9826d