Should I use SQL triggers?

Question

Hello,

I am implementing a data base design that has a vehicle table, vehicle engine and vehicle gear table with SQL 2005.

Each table has an ID that is a SQL identity number, and each engine and gear has a relation with the vehicle ID. So before I create a vehicle I must create an engine and gear.

How could I know the vehicle identity number when creating the engine and gear? The vehicle row hasn't yet been created because of the foreign key constraint with the engine and gear tables?

Should I implement an automatic trigger that on a creation of a vehicle creates an empty row for the engine and gear linked to the vehicle? But again how could I know the vehicle ID?

Thanks,

Omri

Answer 1

Will you only ever have one engine and one gear for each vehicle? If that's the case, then why are they in different tables?

If you must have them in two tables, you can create an INSTEAD OF INSERT trigger as described here: http://msdn.microsoft.com/en-us/library/ms175089.aspx

Answer 2

Are you sure you got the tables design right? I don't really understand what kind of relationships you have between you entities. For example: are you trying to create one to many or many to one relationship between Vehicle and Engine?

One option could be (if it meets your needs):

Vehicle: (ID, EngineID, GearID, ...)

Engine (ID, other engine data)

Gear (ID, other gear data)

Answer 3

If you must have two separate tables, you could join them in a view then update/insert into the view ...

CREATE VIEW VehicleComplete AS SELECT * FROM Vehicle INNER JOIN VehicleEngine USING(VehicleID)

UPDATE VehicleComplete SET Rego = 'ABC 123', EngineModel = '380' WHERE VehicleID = 1

Answer 4

Whichever, there's no need to create rows in any of the tables for which you don't have data. It's fine to, for instance, have an engine row that is unmatched to a vehicle. You can add what's missing when you find it.

I think I understand the design. Each vehicle can have one engine and one transmission. But you may record the transmission or engine before you find the vehicle.

They really are separate entities, so treat them as such. You may easily end up with engines and transmissions which you never match to a vehicle.

Another more interesting question is whether you might end up with a transmission matched to an engine, but no vehicle. One often sees an engine and tramsission bolted together with no vehicle in sight. And they'll often be sold together as well.

In fact, you can imagine any of the three existing alone, or matched to one or two of the other entities.

Triggers have no role here. If used at all, triggers should be restricted to fine-grained referential integrity rules about the schema structure - not for business rules like this. There are no mandatory constraints - every foreign key is optional (nullable). And there are several different ways to set the FK fields up.

Answer 5

I have to say that personally I believe this scenario doesn't make sense, really.

In order for a gear or engine to exist, you need a vehicle. Your business rules/Domain Model should really enforce that, and the foreign key relationships are then just a way of validating this is the case.

Alternatively it might be better to think of the engine-vehicle relationship as a many-many. For this you need an additional table to link the vehicle and engine. This would mean you can have foreign key constraints, but it also means that an engine can be linked to many cars, and vice versa. This more realistically models the real-world, where the same engine is used in many models of car, and a car may have a variety of engines to chose from.

Answer 6

These kind of things are solved with any persistence framework out there. In a typical O/R mapper scenario, you simply create the entities required (indirectly for example) and the O/R mapper saves them in the right order and syncs the FK/PK fields automatically.

If you're fighting with these kind of problems, you really lose time (and thus money) over things which are already solved for you and you can't spend that time on the problem for the customer. So do yourself and your customer a favor and at least look at some of the persistence frameworks out there.

(disclaimer: I'm the lead developer of an o/r mapper framework)

Answer 7

But again how could I know the vehicle ID?

There is a table called INSERTED which is available in insert triggers. You will find the vehicle ID there.

Answer 8

If you really can't create each entity at diferent moments, you should create them within a transaction, explicitly showing at are the necessary steps to create each entity.

The trigger solution, which is many times the easier to implement, would be the hardest to mantain, because it "hides" behind the curtins important business rules.

Well .... in either case, you really should take a look at your problem definition, as this kind of questions usually arrise from bad business domain definition.

Bottom line: each trigger you have would be another headache for the next project iteraction.

Answer 9

The fact that you have foreign key relationships between your tables does not mean that you have to create your data in a particular order. Normally, one would expect the Vehicle record to be created first, and then have engines and gears assigned to it later, but this does not have to be the case.

If, in your scenario, it is possible for engines or gears to be recorded in the database, before being assigned to a vehicle, then you will need to make the FK columns which reference Vehicle ID allow nulls. These can then be linked to a vehicle id once the vehicle row has been created.

Alternatively, you can create your Vehicle record, then assign engine and gear records to it as they are created.

Answer 10

Thanks for all the answers.

Actually I meant: Vehicle: (ID, other Vehicle data, ...) Engine (ID, VehicleID, other engine data) Gear (ID, VehicleID, other gear data)

So each vehicle can have multiple engines and gears… (Yes I know that in the real world an engine can fit multiple cars but that’s not my target)

I wasn't aware of the enforce foreign key constraint that by default is set to YES in SQL 2005.

So set it to NO and know its working just fine.

Thanks again,

Omri.