我在子表上创建了一个INSTEAD OF INSERT触发器,它将在必要时自动在父表中创建一条记录。我已经让触发器工作了,但我担心这将是一个令人头痛的维护问题,因为我必须显式列出子表中的每个非自动编号字段。
下面是工作SQL,应该演示我想要完成的:
CREATE TABLE Accts
(AcctNum char(3) NOT NULL CONSTRAINT PK_Accts PRIMARY KEY
,StatusCode char(1))
CREATE TABLE Docs
(DocID int NOT NULL IDENTITY (1,1) CONSTRAINT PK_Docs PRIMARY KEY
,AcctNum char(3) NOT NULL CONSTRAINT FK_Doc_AcctNum FOREIGN KEY REFERENCES Accts(AcctNum)
,SavedBy varchar(30) NOT NULL
,SavedAt datetime NOT NULL)
GO
CREATE TRIGGER Tr_I_Docs ON Docs
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Accts (AcctNum, StatusCode)
SELECT DISTINCT i.AcctNum, 'N' FROM inserted as i
WHERE NOT EXISTS
(SELECT 1 FROM Accts AS A
WHERE A.AcctNum=i.AcctNum);
INSERT INTO Docs (AcctNum, SavedBy, SavedAt)
SELECT i.AcctNum, i.SavedBy, i.SavedAt FROM inserted as i;
END
GO
INSERT INTO Docs(AcctNum,SavedAt,SavedBy)
VALUES
('111','2014-03-12','Jim')
,('222','2014-03-13','Joe')
,('333','2014-03-14','Tom')
,('111','2014-03-21','Dan')
SELECT * FROM Accts;
GO
DROP TABLE Docs;
DROP TABLE Accts;
当Docs
表中的列发生变化时,我需要记得更新这个触发器。理想情况下,我想从触发器中替换这些行…
INSERT INTO Docs (AcctNum, SavedBy, SavedAt)
SELECT i.AcctNum, i.SavedBy, i.SavedAt FROM inserted as i;
…像这样:
INSERT INTO Docs SELECT *
显然,由于Docs
表中的IDENTITY列,上述操作将不起作用。有什么建议吗?
除了使用INSTEAD OF INSERT触发器来完成我的目标之外,我也欢迎任何想法。
受到这个答案的启发,我替换了以下几行:
INSERT INTO Docs (AcctNum, SavedBy, SavedAt)
SELECT i.AcctNum, i.SavedBy, i.SavedAt FROM inserted as i;
SELECT * INTO #Docs FROM inserted;
ALTER TABLE #Docs DROP COLUMN DocID;
INSERT INTO Docs SELECT * FROM #Docs;
DROP TABLE #Docs;
大的插入可能是性能方面的问题,但如果应用程序一次只能插入一行或几行,则应该没问题。