1

I have a table e.g. Customers(CustomerType, Name) and two views for each customer type:

create view PremiumCustomers as
(
  select Name from Customers where CustomerType = 1
);

and

create view NormalCustomers as
(
  select Name from Customers where CustomerType = 2
);

Is there a way I could insert into each of these views and set the default value for CustomerType accordingly?

e.g. by using:

INSERT INTO PremiumCustomers (name) VALUES ('foo')

to insert ('foo',2) to the Customers table.

xpy
  • 5,481
  • 3
  • 29
  • 48

1 Answers1

2

Try like this

CREATE TRIGGER TrPremiumCustomersInsteadTrigger on PremiumCustomers
INSTEAD OF INSERT
AS
BEGIN
  INSERT INTO Customers (Name, CustomerType)
       SELECT Name, 1
       FROM inserted
END;


CREATE TRIGGER TrNormalCustomersInsteadTrigger on NormalCustomers 
INSTEAD OF INSERT
AS
BEGIN
  INSERT INTO Customers (Name, CustomerType)
       SELECT Name, 2
       FROM inserted
END;

INSERT INTO PremiumCustomers (name) VALUES ('foo')
INSERT INTO NormalCustomers (name) VALUES ('foo')
StackUser
  • 5,370
  • 2
  • 24
  • 44
  • So, I'll have to make a trigger for every CustomerType? Does this have any performance issues? – xpy May 24 '16 at 07:58
  • @xpy - why ask if there are performance issues, since this seems like the only solution that fits your *requirements*? – Damien_The_Unbeliever May 24 '16 at 08:03
  • There might be a performance hit when you do bulk loading using your view. – StackUser May 24 '16 at 08:05
  • @Damien_The_Unbeliever Because I could instead Insert to the underlying table, or add the `CustomerType` to the view and also pass this value when inserting. Also, I'm not yet convinced it is the only solution. – xpy May 24 '16 at 08:07
  • @xpy - well, if you have other alternatives that don't involve inserting through the views and not specifying the column (which are the only current requirements that we can divine from your question), by all means try out each of the alternatives and [race your horses](https://ericlippert.com/2012/12/17/performance-rant/) – Damien_The_Unbeliever May 24 '16 at 08:09