ERwin Triogger Bug

[John Posada]

Hi, guys...new guy here. Let me introduce:

I'm a technical writer for B&N.com and I've been using ERwin
Datamodeler 4, release 4.1.2771 to create data dictionaries. I'm not a
DBA, though I'm slowly picking stuff up.

I discovered something yesterday that from my perspective is quite
serious and I wanted to verify that it is indeed an issue.

I have bunches of tables and for some tables, I have
triggers...anywhere from 1 to 6 of them for a table. Yesterday, I
noticed that triggers appeared in SQL Query Analyzer that didn't
appear in ERwin and after a long conversation with CA tech support, it
was determined that ERwin doesn't properly support showing all
triggers for a table when more than one trigger for that table
performs the same action; insert, uipdate, or delete. When it finds
more than one, it only shows one of them and ignores the others.

As anyone found this to be true? There is a kludge workaround...use
SQL Enterprise to script out the database, then perform a reverse
engineer on the script rather than the database, but that sucks.

Has anyone else noticed this?

John Posada

[Erland Sommarskog]

John Posada (jp*****@book.com) writes:

I have bunches of tables and for some tables, I have
triggers...anywhere from 1 to 6 of them for a table. Yesterday, I
noticed that triggers appeared in SQL Query Analyzer that didn't
appear in ERwin and after a long conversation with CA tech support, it
was determined that ERwin doesn't properly support showing all
triggers for a table when more than one trigger for that table
performs the same action; insert, uipdate, or delete. When it finds
more than one, it only shows one of them and ignores the others.

As anyone found this to be true? There is a kludge workaround...use
SQL Enterprise to script out the database, then perform a reverse
engineer on the script rather than the database, but that sucks.

I don't use ERwin, but PowerDesigner from Sybase. Whether PowerDesigner
can handle this situation nicely, I don't know, because I don't have
triggers in PowerDesigner, and I never have more than one trigger
per action for the same table.

However, unless the ERwin documentation clearly says that ERwin handles
this, I find it difficult to call it a bug. Sounds more like a limitation
to me. To make an analogy: in PowerDesigner you can only have one table
constraint per table.

As for having more than one trigger per action for a table, I am inclined
to say that just because this feature is there, does not mean that you
have to use it. Since the execution order is undefined, this seems
somewhat risky to me. (Unless you have one INSETEAD OF and one AFTER
trigger for the same action.)
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

John Posada (jp*****@book.com) writes:

Second, about it not being a bug, I'm not asking ERwin to allow me to
create more than one trigger...that situation already exists. I'm only
asking it to show me an accurate representation of what it is.

If ERwin does not have the capabilities to create more than one trigger
on a table, I would expect it to be able to display more than one either.

I did an experiment with PowerDesigner and reverse-engineered a table
which have a load of table constraints. (As I mentioned, PD does not
support more than one table constraint per table.) And sure enough,
only one of the constraints made it into PD.

So, unless you can find some documentation that says that ERwin handles
multiple triggers on a table, my conclusion is that you have run into a
limitation of the tool, and the best suggestion for you would be to
use what even forum for enhancement requestions that CA provides.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[John Posada]

Thanks, I'm working on it.

John Posada

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!