Future-proofing an INSTEAD OF INSERT trigger

Question

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.

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;

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 *

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

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

@RemusRusanu: I'm not sure I know exactly what you are referring to. Are you suggesting code generation for the entire schema, or just this trigger, or is there some sort of code generation integrated into SQL Server itself? A quick link would be helpful. Thanks. — mwolfe02, Mar 12 '14 at 16:59

score 1 · Accepted Answer · edited May 23 '17 at 12:05

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;

with these lines:

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.

Future-proofing an INSTEAD OF INSERT trigger

1 Answers1

Linked