I think the conclusion that case 3 is prefereable is flawed for the architectural reason. Number one rule or OO design is "Program against interfaces, not objects". This approach should also be applied to database sesign as well. Views are "interfaces" and tables are "objects". Without triggers this would not be possible.

Another downside to using stored procedures instead of views&triggers is that they do not provide enough flexibility. If I were to create a view with INSTEAD OF trigger which does the actual insert, I would be able to use it for individual row inserts or many row inserts. spAddDummyTable1 allows for individual inserts only! If you were required to insert muiltiple rows, you'd have to write another SP or keep calling spAddDummyTable1 for each insert. The former approach duplicates functionality which is bad and the latter causes really bad performance.

Let me offer a solution that uses OO approach while maintaining flexibility and performance:

-- the following two views are INTERFACES to code against

CREATE VIEW IDummy

AS

SELECT * FROM DummyTable1

GO

CREATE VIEW IDummyAudit

AS

SELECT * FROM DummyTable1_Audit

GO

-- the following triggers implement actual inserts

CREATE TRIGGER IDummy_insert ON IDummy INSTEAD OF INSERT

AS

INSERT DummyTable1 SELECT * FROM inserted

INSERT IDummyAudit SELECT *,'N' from inserted

GO

CREATE TRIGGER IDummyAudit_insert ON IDummyAudit INSTEAD OF INSERT

AS

INSERT DummyTable1_Audit SELECT * FROM inserted

GO

Now, you can code against interfaces(IDummy and IDummyAudit) instead of actual table objects while hiding functional code in the trigger. You can also use INSERT's and SP's, single or multi-row inserts freely.

NOTE: For the purposes of brevity, I've used * instead of explicit column listing. It's a good coding practice to list columns explicitly.