When I desing the ER model I want to use mixed case naming but in my tables I want to have all the names uppercase. Is there a way to automatically change the names uppercase? YES!

These are my tables and as you can see the names of the tables and some of the columns are very mixed cased notation. What I need now is a Transformation Script:

In the Custom Trasformation Scripts I can find a script called Tables to upper case – Rhino.

I press Apply. And this is what I get:

Every single table in our database has columns USER_CREATED, USER_MODIFIED, DATE_CREATED, DATE_MODIFIED. Is there a way to get Data Modeler to create the triggers for inserting data to those columns? YES!

The thing you need is Table DDL Transformation:

Define a new Table DDL Transformation Script set (in my example Trigger_for_Technical_Column):

Define a After Create script and write something like this to it (thank you Marko Helskyaho 🙂 ):

/*
variable ddlStatementsList should be used to return the list with DDL statements
that are created by script – as shown below:
ddlStatementsList.add(new java.lang.String(ddl));
other available variables:
– model – relational model instance
– pModel – physical model instance
– table – the table in relational model
– tableProxy – table definition in physical model
*/
var ddl;
var lname;
// trigger name prefix
trgPre = “BIU_”;
// trigger name suffix
trgSuf = “_TRG”;
prompt = model.getAppView().getSettings().isIncludePromptInDDL();
useSchema = model.getAppView().getSettings().isIncludeSchemaInDDL();
if(model.getStorageDesign().isOpen()){
if(useSchema){
lname = tableProxy.getLongName();
}else{
lname = tableProxy.getName();
}
}else{
if(useSchema){
lname = table.getLongName();
}else{
lname = table.getName();
}
}

if(prompt){
ddl= “\nPROMPT Creating Trigger for ‘”+lname+”‘;\n”;
}else{
ddl = “\n”;
}
ddl = ddl +
“CREATE OR REPLACE TRIGGER “+trgPre+lname+trgSuf +”\n”+
” BEFORE \n” +
” INSERT OR \n” +
” UPDATE ON “+lname+” FOR EACH ROW \n” +
” BEGIN \n” +
” IF INSERTING THEN \n” +
” :NEW.DATE_CREATED := SYSDATE; \n” +
” :NEW.USER_CREATED := NVL(WWV_FLOW.G_USER,USER); \n” +
” END IF; \n” +
” :NEW.DATE_MODIFIED := SYSDATE; \n” +
” :NEW.USER_MODIFIED := NVL(WWV_FLOW.G_USER,USER); \n” +
” END; \n” +
” /”
ddlStatementsList.add(new java.lang.String(ddl));

You can immediatelly test it. Select one of your tables as Table To Test (in my example Isa) and press Test. This is what I got:

Looks what I wanted but how can I use this script?

When generating DDLs select Include Table DDL Scripts tab. Then select the script from Active Script Set list and select those tables you want to execute it:

OK. And this is what I got:

CREATE TABLE isa (
avain_uusi NUMBER(16) NOT NULL,
jokumuu VARCHAR2(50 CHAR) NOT NULL,
user_created VARCHAR2(50 CHAR) NOT NULL,
user_modified VARCHAR2(50 CHAR),
date_created DATE NOT NULL,
date_modified DATE
);

ALTER TABLE isa ADD CONSTRAINT isa_pk PRIMARY KEY ( avain_uusi );

CREATE OR REPLACE TRIGGER BIU_Isa_TRG
BEFORE
INSERT OR
UPDATE ON Isa FOR EACH ROW
BEGIN
IF INSERTING THEN
:NEW.DATE_CREATED := SYSDATE;
:NEW.USER_CREATED := NVL(WWV_FLOW.G_USER,USER);
END IF;
:NEW.DATE_MODIFIED := SYSDATE;
:NEW.USER_MODIFIED := NVL(WWV_FLOW.G_USER,USER);
END;
/

CREATE TABLE lapsi (
avainlapsi NUMBER(16) NOT NULL,
lisatieto VARCHAR2(50 CHAR) NOT NULL,
avain_uusi NUMBER(16) NOT NULL,
user_created VARCHAR2(50 CHAR) NOT NULL,
user_modified VARCHAR2(50 CHAR),
date_created DATE NOT NULL,
date_modified DATE
);

ALTER TABLE lapsi ADD CONSTRAINT lapsi_pk PRIMARY KEY ( avainlapsi,avain_uusi );

CREATE OR REPLACE TRIGGER BIU_lapsi_TRG
BEFORE
INSERT OR
UPDATE ON lapsi FOR EACH ROW
BEGIN
IF INSERTING THEN
:NEW.DATE_CREATED := SYSDATE;
:NEW.USER_CREATED := NVL(WWV_FLOW.G_USER,USER);
END IF;
:NEW.DATE_MODIFIED := SYSDATE;
:NEW.USER_MODIFIED := NVL(WWV_FLOW.G_USER,USER);
END;
/

ALTER TABLE lapsi
ADD CONSTRAINT lapsi_isa_fk FOREIGN KEY ( avain_uusi )
REFERENCES isa ( avain_uusi );

I always define columns like USER_CREATED, USER_MODIFIED, DATE_CREATED, DATE_MODIFIED for my tables but I would not like to design them in the logical model. Is there a way to get them automatically to the relational model? YES!

Here’s my logical model. None of my entities has those attributes defined.

First design a table that only has those technical columns, nothing else. In my example a table is called TempTable:

Then when Engineering to Relational Model select General Options, Use Template Table and select this table from the list:

Engineer.

Now all the tables you just generated to a relational model has these columns: