面向未来的INSTEAD OF INSERT触发器 [英] Future-proofing an INSTEAD OF INSERT trigger

问题描述

我在子表上创建了INSTEAD OF INSERT触发器，如有必要，该触发器将自动在父表中创建记录。我有触发器在工作，但恐怕这将使维护工作头痛不已，因为我不得不在子表中明确列出每个非自动编号字段。

I've created an INSTEAD OF INSERT trigger on a child table that will automatically create a record in the parent table if necessary. I have the trigger working, but I am afraid it will be a maintenance headache moving forward as I had to explicitly list each non-autonumber field in the child table.

以下是正在运行的SQL，它应该演示我要完成的工作：

The following is working SQL which should demonstrate what I am trying to accomplish:

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 表中的列已更改我需要记住要更新此触发器。理想情况下，我想从触发器中替换这些行...

As columns are changed in the Docs table I will need to remember to update this trigger. Ideally, I would like to replace these lines from the trigger...

INSERT INTO Docs (AcctNum,   SavedBy,   SavedAt)
SELECT          i.AcctNum, i.SavedBy, i.SavedAt FROM inserted as i;

...还有更多类似的内容：

...with something more like this:

INSERT INTO Docs SELECT * 

显然，以上由于文档表中的IDENTITY列而无法使用。有任何建议吗？

Obviously, the above won't work due to the IDENTITY column in the Docs table. Any suggestions?

除了使用INSTEAD OF INSERT触发器来实现我的目标外，我也欢迎提出建议。

I'd also welcome ideas besides using an INSTEAD OF INSERT trigger to accomplish my goals.

推荐答案

灵感来自这个答案，我替换了以下几行：

Inspired by this answer, I replaced these lines:

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;

大插入在性能上可能是个问题，但是如果应用程序仅限于插入一行或一次几个就可以了。

Large inserts could be a problem performance-wise, but if the application is limited to inserting rows one or a few at a time it should be OK.

这篇关于面向未来的INSTEAD OF INSERT触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！