you're reading...
PowerDesigner, XEM

Events – Initialize

This will be the first of several posts on events.  As many of my friends and colleagues have pointed out, I don’t have a great track record putting out new posts on a regular basis.  So, right now, I’m committing to at least one more post on this topic.  The Sybase documentation lists almost a dozen different events, and I’m fairly certain their list is either incomplete or there’s another list somewhere I haven’t found.  I’m not giving myself and end date (baby steps), but it’s a start.  One of the reasons I started this blog was to force myself to learn a bit more about PowerDesigner and vbScript so this should be interesting.  Some of these events, I haven’t used yet, so honestly I’m not quite sure what I’ll write about but I’m sure I’ll figure something out.


All metaclasses support 4 events:  Initialize, Can Create, Validate, and On Target Model Reconnection.  There are a few additional methods that apply to specific objects which I may or may not cover.

You add events, just like you add anything else.  By extending a model through an XEM or XDB file.  I’ll use an XEM for my examples.  I’ll assume you know how to get that far (as usual).  For my demonstration, I’m going to create an initialization even on the table metaclass that adds a pair of columns (create date, update date).  I’ve tried to do as much of this as possible to save myself time.  Originally I automated these types of tasks with custom checks and auto-corrects, but initialization events are a much better method.  Why create code to fix something you’ve already done wrong or forgotten to do when you can create code to do it automatically as you create the object.  Faster, better, harder to forget, and usually less code.

Adding our Event

So, you’ve created or opened an XEM file and added the table metaclass.  If you’re not sure how to do this, see several of my prior posts.

Step 1:  Add an event

Right click on the table metaclass, you’ll see a list of 5 events (table has an extra).  Check the box next to Initialize and click ‘OK’
Now, you can open the event and edit the initialize event.  However, as usual, I’ll make things more complex than they have to be (occasionally this pays off in the long run) and put my code in a custom method.  Why?  Because I have some models that need these columns added to existing tables and doing it this way makes it easy to add the columns to existing tables and automatically to new tables with a single piece of code.

Step 2:  Create our Custom Method

Right click on the table metaclass again and create a new method.  Here’s the code I’ll use.  I’m keeping it simple for demonstration purposes.  I called my method addAuditColumns.

Sub %Method%(obj)
   ' Implement your method on <obj> here

   dim newCol

   'Create Date
   set newCol = obj.Columns.CreateNew()

   with newCol
      .Name = "Create Date"
      .SetNameAndCode .Name, "", true
      .DataType = "DATE"
      .Mandatory = TRUE

   end with

   'Update Date
   set newCol = obj.Columns.CreateNew()

   with newCol
      .Name = "Update Date"
      .SetNameAndCode .Name, "", true
      .DataType = "DATE"
      .Mandatory = TRUE
   end with

End Sub

I’ll add a menu item to the table metaclass as well so I can call this method with a couple of clicks on the context menu.  I’ve included it in the demo file.

Step 3:  Call the Custom Method

Now that we’ve done all the hard work, in our method, the event becomes easy.

Here’s the code:

Function %Initialize%(obj)
   ' Implement your initialization on <obj> here
   ' and return True in case of success

   obj.ExecuteCustomMethod("Events.addAuditColumns")   %Initialize% = True
End Function

And we’re done.  Now, anytime you create a table, you’ll automatically start with your two audit columns.

Step 4:  Create Some Tables

Best of luck.



6 thoughts on “Events – Initialize

  1. Hi Rick, A very interesting post on audit columns. This will handle automatically adding audit columns to a physical model. I was wondering how you would advise the addition of audit columns between the merge of the logical model to the physical model (i.e logical model does not have audit columns, but the physical model does). Would you advise a post generation script?

    Posted by Maeve | June 16, 2011, 4:18 am
  2. By script, I assume you mean transform? That would be my preferred solution. I’ve used both pre and post generation transforms. I have multiple target platforms and pre-generation allows me to take care of some of the data type conversions that vary by RDBMS (e.g. varchar2 vs. varchar or date vs. timestamp). If you’re using database specific features like defaults, triggers, etc. or only supporting a single platform then post-generation probably makes more sense.

    Posted by Rich Kier | June 16, 2011, 10:44 am
  3. Hi Rich, thanks for the reply – I think I will leave it as a post generation transform.
    Your blog is really interesting, keep up the good work!

    Posted by Maeve | June 21, 2011, 4:48 am
  4. Hi Rich, a further question for you. When adding audit columns to the physical model only .. the merge dialog box will always report a difference between a logical entity and a physical table (physical has audit columns). Is there any way to disable that so the real differences are apparent in a merge?

    Posted by Maeve | June 29, 2011, 4:55 am
    • This might work (but probably not)…
      1) create an extended collection “AuditColumns” on the table
      2) have your transform add the columns you add to that collection
      3) change the merge comparison options to ignore that collection

      I think this will cause your audit columns to not get generated, but that’s the only thing I can think of that might work. No time to test tonight, but if you try it out, please post your results.


      Posted by Rich Kier | June 29, 2011, 9:25 pm


  1. Pingback: Events – Before Database Generate (More or less) « Power Designing - October 9, 2011

Leave a Reply to Rich Kier Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: