New Features
Enhanced Features
O/R Mapping
Tips and Tricks
UML Diagrams
VP Suite

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.



Modeling Stored Procedure and Database Trigger

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.

Stored Procedure

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.

Database Trigger

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.

Database Engineering with Stored Procedure and Database Trigger

Generating Database Schema from ERD with Stored Procedure and Database Trigger

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.


Reversing Stored Procedure and Database Trigger from Database

  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.


Supported Databases

Stored Procedure Trigger
MySQL Generate/Reverse Generate/Reverse
MSSQL Server Generate/Reverse
(Not support ResultSet)
Generate/Reverse
Oracle Generate/Reverse Generate/Reverse
HSQL Generate Not supported
Sybase ASE Generate/Reverse Generate/Reverse
Sybase SQL Anywhere Generate/Reverse Generate/Reverse
PostgreSQL Generate/Reverse
(8.0 or above)
Generate/Reverse
Cloudscape/Derby Generate Not supported
DB2 Generate Not supported
Ingres Generate Not supported
OpenEdge Generate Not supported
Informix Generate Not supported
Firebird Generate Not supported
FrontBase Generate Not supported

Related Articles

Resources

 
 
Last modified: 2007/09/28 13:01
 
 
Home | Recent Topics | Highlights | UML Diagrams | Tips and Tricks | Object-Relational Mapping
visual-paradigm.com Home | Training Center | UML Center | VP Gallery | Discussion Forum | UML Open Directory