2

I am learning SQL and have some tables much like the following:

Person (id*, name)
Customer(id*, is_active, ...)
Employee(id*, department_id, ...)

(the * indicates the primary key, which in the case of Customer and Employee is both a PK and a FK back to Person)

Both Customer and Employee are types of Person, and I want to ensure that when a record is inserted into Person a record must also be inserted into EITHER Customer or Employee but NOT both. A Person cannot be both an employee and customer within the limits of this example.

I have been told that a Trigger would be useful to enforce this constraint. Could somebody please explain the usage of a trigger with this simple example?

John Woo
  • 258,903
  • 69
  • 498
  • 492
xingyu
  • 312
  • 1
  • 3
  • 13
  • You don't have to use triggers. You can make a proceudre that inserts the 2 rows. – ypercubeᵀᴹ Sep 11 '12 at 06:33
  • Which DBMS are you using? Oracle? PostgreSQL? –  Sep 11 '12 at 09:49
  • Are you sure a trigger is the way you want to go? From the design you have here, a trigger might not have all the information it needs to correctly populate either of the two tables you want to populate. You might want to handle the insert as part of transaction in your application code – kolossus Sep 12 '12 at 06:51
  • DBMS is PostgreSQL. I am just concerned that I need to enforce the above rules and understood that a trigger might be the best way to do this. I suppose doing everything as part of a transaction would also ensure this, but it assumes the operator does the when inserting/updating/deleting rows – xingyu Sep 13 '12 at 01:25

3 Answers3

3

you can use INSTEAD OF triggers. Your question is fully covered in this MSDN Article (Designing INSTEAD OF Triggers)

.

Maryam Arshi
  • 1,974
  • 1
  • 19
  • 33
2

Depending on what your DBMS supports and other factors, you can either:

  • Insert via a stored procedure that is implemented to ensure the correct behavior.
  • Or use a trigger on a view (that JOINs a supertype with particular subtype) to make the view "updatable" and then insert into the view.
  • Or ensure both exclusivity and presence of subtypes through purely declarative means, as explained here.
  • Or use an implementation strategy for inheritance different from "all classes in separate tables", as mentioned here.
Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
1

If you want to make sure a person is either Customer or Employee, you can create a column in the Person table called "Type", for example, and it can have values "Customer" and "Employee" or "1" and "2". And then insert relevant info into the Customer or the Employee table, as suggested by the commented above.

Raissa
  • 111
  • 1