Automatically Create Audit Tables

Toad Data Modeler
1

Hi there,

I’m currently modeling a new database in TDM (MSSQL) and need to add audit tables for a handful of entities.

Whilst I could sit here and manually add them, as the number of tables requiring auditing is going to grow over the next year as the database is extended I was wondering whether it’s possible to create a macro that would allow me to select an existing table and for it to automatically copy all of the attributes and descriptions to a new table, to add a further 4 auditing columns including a PK along with a couple of check constraints.

Has anyone attempted this or is there any examples lying around that might help?

Many thanks.

Kevin

2

Hi Kevin,

it is possible to write or modify a macro that will do majority of tasks you require. I’d like to note that some of the functionality might already be available, see the following page:
http://www.casestudio.com/help/ProductivityPack.aspx

And if you wish to define new table with predefined columns, you can use gallery.
Click Help | Help Topics and navigate to page Help | Features | Gallery.

Regards,

Vaclav

3

Hi Vaclav,

Thanks for the reply.

I thought I’d get on and have a go at creating a script from scratch yesterday and so far I’ve managed to get it to create the entity, add the additional attributes along with descriptions and schema information and to also copy all of the existing attributes from a table.

I’m currently trying to create a new PK, but having a couple of difficulties. Do you have a code snippet lying around that shows how to add a PK?

So far I’ve got:

     pk = nEntity.CreateNewObject(2010);
     pk.Caption = "PK_" + nEntity.Name + "_" + nEntity.Name + "ID";
     pk.Name = "PK_" + nEntity.Name + "_" + nEntity.Name + "ID";
    
     item = pk.CreateNewObject(2011);
     item.SetLinkedObject("Attribute", nEntity.Attributes.GetObject(0));
    
     pk.SetLinkedObject("KeyItems", item);
     pk.Comments = "Primary key (clustered) constraint.";

Which seems to create a key OK, but I can’t figure out how to make it a PK.

Also, is there a property or option to set whether a key is clustered or non-clustered?

Many thanks,

Kevin

4

I think I’ve figured it out:

     attribute = nEntity.Attributes.GetObjectByName(nEntity.Name + "ID");

pk = nEntity.PK;
pk.Caption = “PK_” + attribute.Caption;
pk.Name = “PK_” + attribute.Name;
pk.AddAttribute(attribute);
pk.Comments = “Primary key (clustered) constraint.”;
pk.InVisible = false;
pk.CommitChanges();

and to set clustered/non-clustered:

     pk.Clustered = true/false;
5

Congratulations :slight_smile:

More info: there always is one empty primary key in each new table. Therefore you don’t create new primary key, but set the InVisible param to false.

Unfortunately, it is not possible to change primary key via scripting in the current version of TDM. I mean if you created another key and wanted to change the existing primary key to the second key, that would be possible to do manually only.

Regards,

Vaclav

6

Hi Vaclav,

I’ve now finished the script and added it as a Macro :slight_smile:

The only problem I’m having is whilst it appears in the ‘Macros’ menu, if I right click on an entity and go to ‘Macros’, it doesn’t appear…?

In the ‘Macro Properties’ I’ve got ‘Add To Popup Menu’ ticked with an empty ‘Path:’.

On the ‘Object Types’ tab, I’ve selected ‘Custom’ and ticked ‘Entity’ under the ‘Physical Model’.

Any ideas?

Kevin

7

Hi Kevin,
if you right click on an entity in Model Explorer it will appear. If you want same behaviour on workspace you need select on the ‘Object Types’ tab ‘Entity Shortcut’ (under Workspace).

If you do it, you need check type of object in This.GetObject(i); in macro. It may be Entity, or Entity Shortcut. You can use something similar:

var Obj = This.GetObject(i);
if (Obj.ObjectType != 2002) //2002 is Object Type of PER Entity
Obj = Obj.ParentBase;

Petr

8

Hi Petr,

Many thanks for that - All working now :slight_smile:

Kevin