How can triggers and stored procedures be represented in an ER Diagram?

Question

Is it a good idea to show triggers and stored procedures in an Entity Relationship Diagram? If yes, what is the notation? Information seems really scarce on this topic. Also, are there any UML diagrams you would use to represent triggers?

Guava

Answer 1

Since Triggers are based on actions in sequence, I haved used a UML sequence diagram rather than an ERD. The swimlanes would be the base tables and the messages would be the triggers firing. For stored procedures, the same approach could be taken if the stored procedures were centered around a certain table. If not, a collaboration diagram could be used.

Prong

Makes sense. It's going to be either swimlanes, activity diagrams or sequence diagrams. – Guava

Answer 2

As Steve said, you may represent the dynamic of a trigger or stored proc in a dynamic diagram. But for triggers specifically, if you just want to declare them, you can also add them as operations in classes used to represent the corresponding tables in your Entity Relationship Diagram. In this case, you might want to use relevant stereotypes on these operations, such as <<trigger>>.

Figone

Answer 3

ER diagrams have never been intended for that purpose and are therefore not very well suited.

The UML event sequences (swimlanes) as suggested elsewhere, might be a tad better, but imo it's still problematic. The arrows cannot distinguish between an insert and a delete, hence you'd have to have several distinct charts for each table (one for what happens upon insert and one for what happens upon delete, and then there's still the gray area of update). I'm also not very sure whether the swimlanes charts allow to document very well what precisely happens to the other (targeted) tables. Insert or delete ? Of which row(s) exactly ? etc. etc.

Verb

Answer 4

The fundamental objective of an Entity Relationship Diagram (ERD) is to illustrate the database's structure, including its entities, relationships, and properties. Triggers and stored procedures are often viewed as being a component of the database technology rather than the conceptual or logical design, which the ERD mostly represents. Thus, it is unusual for an ERD to directly display triggers and stored processes.

However, you may infer the existence of triggers and stored processes by adding annotations or other metadata to an ERD. These annotations might demonstrate the links between certain entities or relationships and stored processes or triggers. This helps to fully understand how the database implementation was implemented.

Methylamine