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 );
