How to automatically create rows and pass values to other tables

Question

There are three tables in database:

1. "BusinessEntity " which has the identity column "BusinessEntityID" as Primary Key (as well as rowguid and ModifiedDate columns).
2. "Firm" which has similarly the identity column "BusinessEntityID" as Primary Key, which is also a Foreign Key to BusinessEntity.BusinessEntityID (it has a 1-to-1 relationship with "BusinessEntity" table, FirmName, rowguid and ModifiedDate columns ).
3. "Customer" which has the identity column "CustomerID" as Primary Key and column "FirmID" as Foreign Key to Firm .BusinessEntityID (plus CustomerName, rowguid and ModifiedDate columns).

i.e. (also see image)

tables:  BusinessEntity            Firm                          Customer
columns:                                                         CustomerID (PK)
         BusinessEntityID(PK) -->  BusinessEntityID (PK/FK) -->  FirmID (FK)

What I'm trying to do is whenever a new Customer row is to be created: A new BusinessEntity row to be created automatically and then pass its BusinessEntityID value to an (automatically) newly created Firm row which it turn would pass its own BusinessEntityID to Customer table as FirmID column.

As you can see a BusinessEntity row was no meaning unless it corresponds to a Firm (or other entities) and a Customer must include a Firm.

I created a view containing all three tables along with a trigger to do the job without success. Any suggestions?

The tables:

1. BusinessEntity

   CREATE TABLE [dbo ].[BusinessEntity](
      [BusinessEntityID] [int] IDENTITY(1,1) NOT NULL,
      [rowguid] [uniqueidentifier] NOT NULL,
      [ModifiedDate] [datetime] NOT NULL,
   
   CONSTRAINT [PK_BusinessEntity_BusinessEntityID] PRIMARY KEY CLUSTERED
   (
      [BusinessEntityID] ASC
   )
   ) ON [PRIMARY]
   GO
   
   ALTER TABLE [dbo].[BusinessEntity] ADD  CONSTRAINT [DF_BusinessEntity_rowguid]  
    DEFAULT (newid()) FOR [rowguid]
   GO
   
   ALTER TABLE [dbo ].[BusinessEntity] ADD  CONSTRAINT [DF_BusinessEntity_ModifiedDate]  
     DEFAULT (getdate()) FOR [ModifiedDate]
   GO
   

2. Firm

   CREATE TABLE [dbo].[Firm](
   [BusinessEntityID] [int] IDENTITY(1,1) NOT NULL,
   [FirmName] [nvarchar](30) NULL,
   [rowguid] [uniqueidentifier] NOT NULL,
   [ModifiedDate] [datetime] NOT NULL,
   
   CONSTRAINT [PK_Firm_BusinessEntityID] PRIMARY KEY CLUSTERED
      (
      [BusinessEntityID] ASC
      )
      ) ON [PRIMARY]
   GO
   
   ALTER TABLE [dbo].[Firm] ADD  CONSTRAINT [DF_Firm_rowguid]  
     DEFAULT (newid()) FOR [rowguid]
   GO
   
   ALTER TABLE [dbo].[Firm] ADD  CONSTRAINT [DF_Firm_ModifiedDate]  
     DEFAULT (getdate()) FOR [ModifiedDate]
   GO
   
   ALTER TABLE [dbo].[Firm]  WITH CHECK ADD  CONSTRAINT [FK_Firm_BusinessEntity_BusinessEntityID] FOREIGN KEY([BusinessEntityID])
     REFERENCES [dbo].[BusinessEntity] ([BusinessEntityID])
   GO
   
   ALTER TABLE [dbo].[Firm] CHECK CONSTRAINT [FK_Firm_BusinessEntity_BusinessEntityID]
   GO
   

3. Customer

   CREATE TABLE [dbo].[Customer](
   [CustomerID] [int] IDENTITY(1,1) NOT NULL,
   [FirmID] [int] NULL,
   [CustomerName] [nvarchar](28) NULL,
   [rowguid] [uniqueidentifier] NOT NULL,
   [ModifiedDate] [datetime] NOT NULL, 
   
   CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED
     (
     [CustomerID] ASC
     )
     ) ON [PRIMARY]
   GO
   
   ALTER TABLE [dbo].[Customer] ADD  CONSTRAINT [DF_Customer_rowguid]                  
      DEFAULT (newid()) FOR [rowguid]
   GO
   
   ALTER TABLE [dbo].[Customer] ADD  CONSTRAINT [DF_Customer_ModifiedDate]  
      DEFAULT (getdate()) FOR [ModifiedDate]
   GO
   
   ALTER TABLE [dbo].[Customer]  WITH CHECK ADD  CONSTRAINT [FK_Customer_Firm_FirmID] FOREIGN KEY([FirmID])
      REFERENCES [dbo].[Firm] ([BusinessEntityID])
   GO
   
   ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [FK_Customer_Firm_FirmID]
   GO
   

Something weird happens here. I created this stored procedure:

CREATE PROCEDURE [dbo].[CreateFirmCustomer](@FirmName NVARCHAR(30), @CustomerName NVARCHAR(28)) AS 
BEGIN;
SET NOCOUNT ON;

BEGIN TRANSACTION;

INSERT BusinessEntity DEFAULT VALUES;
DECLARE @BusinessEntityID INT = SCOPE_IDENTITY();

SET IDENTITY_INSERT [dbo].[Firm] ON

INSERT Firm(BusinessEntityID, FirmName)
VALUES (@BusinessEntityID, @FirmName);

SET IDENTITY_INSERT [dbo].[Firm] OFF

INSERT Customer(FirmID, CustomerName)
VALUES (@BusinessEntityID, @CustomerName);
DECLARE @CustomerID INT = SCOPE_IDENTITY();

SELECT @BusinessEntityID AS FirmID, @CustomerID AS CustomerID;

COMMIT;
END;

GO

When I run it sometimes the CustomerID column gets the value of BusinessEntityID column when it should really be independently auto-generated. Also the BusinessEntityID column auto-generates weird values e.g. jumped from value 7 to value 1002. (BusinessEntityID is BusinessEntity.BusinessEntityID ) Any clues? (see picture)

Now I created this view to insert Customers as Firms:

CREATE VIEW [dbo].[vBusEntityFirmCustomer]
AS
SELECT dbo.Firm.FirmName, dbo.Customer.CustomerName
FROM dbo.BusinessEntity INNER JOIN
     dbo.Firm ON dbo.BusinessEntity.BusinessEntityID = dbo.Firm.BusinessEntityID INNER JOIN
     dbo.Customer ON dbo.Firm.BusinessEntityID = dbo.Customer.FirmID
GO

And this trigger on the view:

CREATE TRIGGER [dbo].[trg_FirmCustomer]
ON [dbo].[vBusEntityFirmCustomer]
INSTEAD OF INSERT
AS
   exec [dbo].[CreateFirmCustomer]
GO

But every time I enter a new FirmName CustomerName to insert a new row I get this message (see image):

Procedure or function 'CreateFirmCustomer' expects parameter '@FirmName', which was not supplied. The fact is that I do supply FirmName.

Answer 1

According to me,

it all depend how and when those 3 tables are populated. Suppose those three table are populated using single UI, then

I will write them in single proc within one transaction.

Suppose those 3 table will be will populated at diff stage i.e diff UI then i write them in diff proc as you have already define constraint.

BTW what is the purpose of rowguid in all 3 tables.

Answer 2

Logically, as designed, you have to create a BusinessEntity first, then a Firm, then a Customer. Across all these tables, the only real information you're storing is the firm name and the customer name -- all the rest is derived and autogenerated by the database. We can encapsulate the operation CreateCustomer in a stored procedure:

CREATE PROCEDURE CreateCustomer(@FirmName NVARCHAR(30), @CustomerName NVARCHAR(28)) AS 
BEGIN;
    SET NOCOUNT ON;

    BEGIN TRANSACTION;

    INSERT BusinessEntity DEFAULT VALUES;
    DECLARE @BusinessEntityID INT = SCOPE_IDENTITY();

    INSERT Firm(BusinessEntityID, FirmName)
    VALUES (@BusinessEntityID, @FirmName);

    INSERT Customer(FirmID, CustomerName)
    VALUES (@BusinessEntityID, @CustomerName);
    DECLARE @CustomerID INT = SCOPE_IDENTITY();

    -- Return IDs of the newly created rows as the result set
    SELECT @BusinessEntityID AS FirmID, @CustomerID AS CustomerID;

    COMMIT;
END;

Invoke as (for example) EXEC CreateCustomer 'Firm', 'Customer'. With the table definitions as given, this will fail because Firm.BusinessEntityID is an IDENTITY -- if it is to take its value from BusinessEntity, it shouldn't be. (You can work around this with IDENTITY_INSERT, but in a properly designed database this shouldn't be necessary.)

Another thing that's obviously weird is that we insert no business data at all in BusinessEntity (which is why we need the DEFAULT VALUES syntax) -- it's nothing but a super-general container of IDs, so it's of dubious value. Nevertheless, this demonstrates the general technique of inserting rows in multiple tables that have dependencies.

As written, this stored procedure always creates a new Firm and BusinessEntity to go along with the Customer. Logically, a Firm can have more than one Customer, so you probably want another stored procedure to create a Customer for an existing Firm. This is simpler, as it's just an INSERT in Customer with the appropriate FirmID. You may wish to have a separate CreateFirm stored procedure that you call first, followed by a CreateCustomer to add a customer for that firm.