Support Stored Procedure and Database Trigger

Stored Procedure is a set of Structured Query Language (SQL) statements with an assigned procedure name that’s stored in the database in compiled form and is part of a relational database. The advantage of a stored procedure is that it is generally faster than doing the same work on a client.

Database trigger is also procedure that is stored in a relational database. It will be executed when a table is modified. A typical use of database trigger is for restricting access to specific data.

With Visual Paradigm’s data modeling and database engineering supports, you can model stored procedures and database triggers, reverse and generate stored procedure and triggers to/from database.

Both stored procedure and database trigger have to be modeled in an Entity Relationship Diagram. With an Entity Relationship Diagram, you can create stored procedure and database trigger by accessing the diagram toolbar.

A stored procedure cannot be modeled alone on a diagram. It must be contained by a stored procedure container. To create such a container:

1. Select Stored Procedures in the diagram toolbar and then click on the diagram.
   
   
   
2. To create a stored procedure, right-click on the stored procedure container and select New Procedure from the popup menu.
   
   
   
3. Enter a name for the stored procedure. The name will not be stored in the database upon database generation. It is just for modeling purpose.
   
   
   
4. Right-click on the created procedure and select Open Specification... from the popup menu.
   
   
   
5. Fill in the Create statements. You need to ensure the correctness of statements if you plan to generate the stored procedure to the database.
   
   
   
6. Press OK to confirm.

Similar to stored procedure, a database trigger cannot be modeled alone on a diagram. It must be contained by a database trigger container. To create such a container:

1. Select Triggers in the diagram toolbar and then click on the diagram.
   
   
   
2. To create a database trigger, right-click on the database trigger container and select New Trigger from the popup menu.
   
   
   
3. Enter a name for the trigger. The name will not be stored in the database upon database generation. It is just for modeling purpose.
   
   
   
4. Right-click on the created trigger and select Open Specification... from the popup menu.
   
   
   
5. Fill in the Create statements. You need to ensure the correctness of statements if you plan to generate the database trigger to the database.
   
   
   
6. Press OK to confirm.

Before generating database schema from ERD, make sure the Create statements for the stored procedures and database triggers have been entered correctly. To generate database:

1. Select Tools > Object Relational Mapping (ORM) > Generate Database... from the main menu.
   
   
   This shows the Database Code Generation dialog box.
2. Check Export to Database to let the generation alter the relationship database.
   
   
   
3. If the target database has not been set, press Database Options to set and configure it.
   
   
   
4. Press OK to proceed with generation. This generates the database schema base on the ERD, which consists of database tables, stored procedures and database triggers. Notice that the drop statements for the procedures and triggers are created as well.
   
   
   

1. Select Tools > Object-Relationship Mapping (ORM) > Reverse Database... from the menu.
   
   
   This shows the Database to Data Model dialog box.
2. Make sure Reverse Stored Procedure and Reverse Trigger are checked and click Next >.
   
   
   
3. Enter the information of the database to reverse. If this will not be changed, select Set as default checkbox, and click Next >.
   
   
   
4. Select the database schema(s) to reverse, and click Next >.
   
   
   
5. Select the database table(s) to reverse, and click Next >.
   
   
   
6. Select the stored procedure(s) to reverse, and click Next >.
   
   
   
7. Select the database trigger(s) to reverse, and click Finish.
   
   
   This will produce an ERD with the selected tables, stored procedures and database triggers.
   
   
   

• See how to generate ORM and access stored procedure
  Stored Procedure
  
  

• Know more about DB Visual ARCHITECT - Java Persistent Code Generator for Relational Database
  http://www.visual-paradigm.com/product/dbva/
  
  
• Look at the features of DB Visual ARCHITECT
  http://www.visual-paradigm.com/product/dbva/features/
  
  
• Get the Free 30 Days Trial of DB Visual ARCHITECT
  http://www.visual-paradigm.com/download/
  
  
• Browse the DB Visual ARCHITECT’s online documentation
  http://www.visual-paradigm.com/documentation/dbvauserguide.jsp
  
  
  • Chapter 4 Object Model - This chapter shows how to depict the object models by using a Class Diagram or an EJB diagram, and describes the mapping from the object model to data model.
    
    
  • Chapter 5 Data Model - This chapter shows you how to depict the object models by using Entity Relationship Diagram and how to reverse database, and describes the mapping from the data model to the object model.
    
    
• View DB Visual ARCHITECT’s Interactive Tutorials
  http://www.visual-paradigm.com/product/dbva/tutorials/