I'm working with a developer that used ERWIN to create the database I have
to interface with. At first glance I noticed that there are a ton of
unecessary triggers, many of which look like they won't even be hit because
they're trying to detect and raise error messages for FK violations (when
there are already FK contraints).
I don't like what I'm seeing so far. Can anyone give me the lowdown about
using this tool with SS2K?
PaulHehe, I've just spent the last two months working with some scripts that
were generated from ERWin.
In a nutshell, the tool is great for reverse engineering a database and
creating pretty DB Diagrams for documentation. Other than this, I don't
like the program myself as I prefer to see the entire table - not just the
columns as FK's, but also datatypes all constraints, indexes and any default
data which is inserted at table creation time.
I'm sure that there are people who love the tool, and to them I say good for
you. It's a personal choice really.
The triggers that your talking about sound like the developer added these
not realising the performance hit. Sounds like you'll have alot of work to
do. Good luck
Regards
Colin Dawson
www.cjdawson.com
"PJ6" <nobody@.nowhere.net> wrote in message
news:%23%23eGBBmpFHA.2888@.TK2MSFTNGP10.phx.gbl...
> I'm working with a developer that used ERWIN to create the database I have
> to interface with. At first glance I noticed that there are a ton of
> unecessary triggers, many of which look like they won't even be hit
> because they're trying to detect and raise error messages for FK
> violations (when there are already FK contraints).
> I don't like what I'm seeing so far. Can anyone give me the lowdown about
> using this tool with SS2K?
> Paul
>|||Erwin is a great tool and I use it religiously. However, it has it's
quirks. You have to be careful to set settings right to make sure that
those triggers aren't created.
I rarely use their native code and constraint generation, so I cannot give
you too much advice, but it is very easy to use and very extensible (which
is why I love it.)
I just created these two tables (ignore the types) and ran it with the
default generation. Everything is good (enough) until the triggers, so I
would just get rid of the triggers.
CREATE TABLE E_1 (
e1Id char(18) NOT NULL,
value char(18) NULL
)
go
ALTER TABLE E_1
ADD PRIMARY KEY (e1Id ASC)
go
CREATE TABLE E_2 (
e1Id char(18) NOT NULL,
value char(18) NULL,
e2Id char(18) NOT NULL
)
go
ALTER TABLE E_2
ADD PRIMARY KEY (e2Id ASC)
go
ALTER TABLE E_2
ADD FOREIGN KEY (e1Id)
REFERENCES E_1 (e1Id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go
create trigger tD_E_1 on E_1 for DELETE as
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* DELETE trigger on E_1 */
begin
declare @.errno int,
@.errmsg varchar(255)
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* E_1 R/1 E_2 ON PARENT DELETE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="E_1"
CHILD_OWNER="", CHILD_TABLE="E_2"
P2C_VERB_PHRASE="R/1", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_1", FK_COLUMNS="e1Id" */
if exists (
select * from deleted,E_2
where
/* %JoinFKPK(E_2,deleted," = "," and") */
E_2.e1Id = deleted.e1Id
)
begin
select @.errno = 30001,
@.errmsg = 'Cannot DELETE E_1 because E_2 exists.'
goto error
end
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
go
create trigger tU_E_1 on E_1 for UPDATE as
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* UPDATE trigger on E_1 */
begin
declare @.numrows int,
@.nullcnt int,
@.validcnt int,
@.inse1Id char(18),
@.errno int,
@.errmsg varchar(255)
select @.numrows = @.@.rowcount
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* E_1 R/1 E_2 ON PARENT UPDATE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="E_1"
CHILD_OWNER="", CHILD_TABLE="E_2"
P2C_VERB_PHRASE="R/1", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_1", FK_COLUMNS="e1Id" */
if
/* %ParentPK(" or",update) */
update(e1Id)
begin
if exists (
select * from deleted,E_2
where
/* %JoinFKPK(E_2,deleted," = "," and") */
E_2.e1Id = deleted.e1Id
)
begin
select @.errno = 30005,
@.errmsg = 'Cannot UPDATE E_1 because E_2 exists.'
goto error
end
end
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
go
create trigger tD_E_2 on E_2 for DELETE as
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* DELETE trigger on E_2 */
begin
declare @.errno int,
@.errmsg varchar(255)
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* E_1 R/1 E_2 ON CHILD DELETE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="E_1"
CHILD_OWNER="", CHILD_TABLE="E_2"
P2C_VERB_PHRASE="R/1", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_1", FK_COLUMNS="e1Id" */
if exists (select * from deleted,E_1
where
/* %JoinFKPK(deleted,E_1," = "," and") */
deleted.e1Id = E_1.e1Id and
not exists (
select * from E_2
where
/* %JoinFKPK(E_2,E_1," = "," and") */
E_2.e1Id = E_1.e1Id
)
)
begin
select @.errno = 30010,
@.errmsg = 'Cannot DELETE last E_2 because E_1 exists.'
goto error
end
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
go
create trigger tU_E_2 on E_2 for UPDATE as
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* UPDATE trigger on E_2 */
begin
declare @.numrows int,
@.nullcnt int,
@.validcnt int,
@.inse2Id char(18),
@.errno int,
@.errmsg varchar(255)
select @.numrows = @.@.rowcount
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* E_1 R/1 E_2 ON CHILD UPDATE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="E_1"
CHILD_OWNER="", CHILD_TABLE="E_2"
P2C_VERB_PHRASE="R/1", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_1", FK_COLUMNS="e1Id" */
if
/* %ChildFK(" or",update) */
update(e1Id)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,E_1
where
/* %JoinFKPK(inserted,E_1) */
inserted.e1Id = E_1.e1Id
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.e1Id is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE E_2 because E_1 does not exist.'
goto error
end
end
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"PJ6" <nobody@.nowhere.net> wrote in message
news:%23%23eGBBmpFHA.2888@.TK2MSFTNGP10.phx.gbl...
> I'm working with a developer that used ERWIN to create the database I have
> to interface with. At first glance I noticed that there are a ton of
> unecessary triggers, many of which look like they won't even be hit
> because they're trying to detect and raise error messages for FK
> violations (when there are already FK contraints).
> I don't like what I'm seeing so far. Can anyone give me the lowdown about
> using this tool with SS2K?
> Paul
>|||I think this is better...
CREATE TABLE E_1(
e1Id char(18) NOT NULL PRIMARY KEY,
value char(18) NULL
)
go
CREATE TABLE E_2 (
e1Id char(18) NOT NULL ADD PRIMARY KEY,
value char(18) NULL,
e2Id char(18) NOT NULL,
CONSTRAINT FOREIGN KEY (e1Id) REFERENCES E_1 (e1Id) ON DELETE NO ACTION
ON UPDATE NO ACTION
)
go
Or better still
CREATE TABLE E_1(
e1Id char(18) NOT NULL,
value char(18) NULL
CONSTRAINT PK_E_1_e1Id PRIMARY KEY (e1Id ASC)
)
go
CREATE TABLE E_2(
e1Id char(18) NOT NULL,
value char(18) NULL,
e2Id char(18) NOT NULL,
CONSTRAINT PK_E_2_e2Id PRIMARY KEY (e2Id ASC),
CONSTRAINT FK_E_2_e1Id FOREIGN KEY (e1Id) REFERENCES E_1 (e1Id) ON DELETE
NO ACTION ON UPDATE NO ACTION
)
go
Here's what's been added - all constraints are now named, the entire table
is now seen and known (I've not included filegroups in the above example
though) The entire table is listed in one foul swoop, everything is shown
together at the same time, with the exception of listing the tables which
depend on this table but that's another issue. These are the things that I
think are lacking from products like ERWin. Don't mistunderstand me, I
think they great tools for showing a table graphically, but for creating
databases... I think there's alot to be desired. Also looking at the
graphical tool, it's not easy to see what check constraints, defaults and
indexes are available - the front end just doesn't show them. ERWin is good
for the job is was designed for though - to show the relationship between
tables.
Regards
Colin Dawson
www.cjdawson.com|||Hi
I'm also new to SQL and am using Xcase Ver 7x Anybody know any advantages,
di
vantages etc with it.
So far it seems to be ok creating, modifying tables, constraints etc
"Programming proves that Natural Selection is true, since every time I
idiot-proof a program a better idiot comes along.
Robert W. Drummond
John Linville
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:zp6Oe.93559$G8.66384@.text.news.blueyonder.co.uk...
>I think this is better...
> CREATE TABLE E_1(
> e1Id char(18) NOT NULL PRIMARY KEY,
> value char(18) NULL
> )
> go
> CREATE TABLE E_2 (
> e1Id char(18) NOT NULL ADD PRIMARY KEY,
> value char(18) NULL,
> e2Id char(18) NOT NULL,
> CONSTRAINT FOREIGN KEY (e1Id) REFERENCES E_1 (e1Id) ON DELETE NO ACTION
> ON UPDATE NO ACTION
> )
> go
> Or better still
> CREATE TABLE E_1(
> e1Id char(18) NOT NULL,
> value char(18) NULL
> CONSTRAINT PK_E_1_e1Id PRIMARY KEY (e1Id ASC)
> )
> go
> CREATE TABLE E_2(
> e1Id char(18) NOT NULL,
> value char(18) NULL,
> e2Id char(18) NOT NULL,
> CONSTRAINT PK_E_2_e2Id PRIMARY KEY (e2Id ASC),
> CONSTRAINT FK_E_2_e1Id FOREIGN KEY (e1Id) REFERENCES E_1 (e1Id) ON
> DELETE NO ACTION ON UPDATE NO ACTION
> )
> go
> Here's what's been added - all constraints are now named, the entire table
> is now seen and known (I've not included filegroups in the above example
> though) The entire table is listed in one foul swoop, everything is shown
> together at the same time, with the exception of listing the tables which
> depend on this table but that's another issue. These are the things that
> I think are lacking from products like ERWin. Don't mistunderstand me, I
> think they great tools for showing a table graphically, but for creating
> databases... I think there's alot to be desired. Also looking at the
> graphical tool, it's not easy to see what check constraints, defaults and
> indexes are available - the front end just doesn't show them. ERWin is
> good for the job is was designed for though - to show the relationship
> between tables.
> Regards
> Colin Dawson
> www.cjdawson.com
>|||Yes, clearly this is better, and why I have built lots of macro code to
implement this in ERwin. Alters are much easier to automatically generate,
especially since you can do them again later (after dropping them of
course.) The company I worked for a long time ago (and I work for them
again today :) had a consultant who also worked with the original company
that owned/wrote the original ERwin product (when it was pretty much all
that there was.) Hence we have grown an insane number of macros that deal
with everything, even turning on and off triggers, custom triggers,
constraints, indexes, everything. It was not easy, but my code is really
really useful, especially during rapid development because I can modify all
of the objects in just minutes.
I occasionally use the base ERwin functionality but only when I am in a
hurry to do something small. I used to try to lobby for changes, but
after CA purchased it I just have never found the right way, and I have all
of this code that works. The only real problem is that it is such a bother
to use I cannot even get coworkers to use it, much less sharing it with the
world. Perhaps I will do something like that and post it on my blog one
day.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:zp6Oe.93559$G8.66384@.text.news.blueyonder.co.uk...
>I think this is better...
> CREATE TABLE E_1(
> e1Id char(18) NOT NULL PRIMARY KEY,
> value char(18) NULL
> )
> go
> CREATE TABLE E_2 (
> e1Id char(18) NOT NULL ADD PRIMARY KEY,
> value char(18) NULL,
> e2Id char(18) NOT NULL,
> CONSTRAINT FOREIGN KEY (e1Id) REFERENCES E_1 (e1Id) ON DELETE NO ACTION
> ON UPDATE NO ACTION
> )
> go
> Or better still
> CREATE TABLE E_1(
> e1Id char(18) NOT NULL,
> value char(18) NULL
> CONSTRAINT PK_E_1_e1Id PRIMARY KEY (e1Id ASC)
> )
> go
> CREATE TABLE E_2(
> e1Id char(18) NOT NULL,
> value char(18) NULL,
> e2Id char(18) NOT NULL,
> CONSTRAINT PK_E_2_e2Id PRIMARY KEY (e2Id ASC),
> CONSTRAINT FK_E_2_e1Id FOREIGN KEY (e1Id) REFERENCES E_1 (e1Id) ON
> DELETE NO ACTION ON UPDATE NO ACTION
> )
> go
> Here's what's been added - all constraints are now named, the entire table
> is now seen and known (I've not included filegroups in the above example
> though) The entire table is listed in one foul swoop, everything is shown
> together at the same time, with the exception of listing the tables which
> depend on this table but that's another issue. These are the things that
> I think are lacking from products like ERWin. Don't mistunderstand me, I
> think they great tools for showing a table graphically, but for creating
> databases... I think there's alot to be desired. Also looking at the
> graphical tool, it's not easy to see what check constraints, defaults and
> indexes are available - the front end just doesn't show them. ERWin is
> good for the job is was designed for though - to show the relationship
> between tables.
> Regards
> Colin Dawson
> www.cjdawson.com
>|||Sounds to me like we're singing from the same hymm sheet. Just different
parts of it. Currently I'm a script jockey, as you saw from my previous
post I like to get things neat done one etc. as DBA for the company that I
work for, I'm trying to tighten up the database structures to make sure that
all foreign keys check constraints proper indexing etc, is in place. As I
don't have an unlimited amount of time to get everything into place and
working 100%, I took the decision early on that ERWin was as you say "it is
such a bother to use". For documentation ERWin is brilliant for creating
documentation, the diagrams are much better looking that the diagrams from
SQL Server, so the company tend to use these. Also lesser experiences
developers can use the tool for creating a basic table structure,
brainstorming and stuff like that to get something off the ground. Once
they're happy with the design, I have to perseude them to generate a script
then go through it with a fine too comb to be sure that they've not missed
anything. After they've finished, it's my turn and I spend ages giving the
developer the third degree and adding about a million extra contraints and
mocking their indexing ideas. However once corrected the database is
extremely well designed. I just wish that ERWin could read in a script and
learn the style that was used to create that script, then when using ERWin
to generate a script it generates a script as close a possible to the
original.
I've waffled for too long on this now.
Regards
Colin Dawson
www.cjdawson.com
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uAtpGSspFHA.3004@.TK2MSFTNGP15.phx.gbl...
> Yes, clearly this is better, and why I have built lots of macro code to
> implement this in ERwin. Alters are much easier to automatically
> generate, especially since you can do them again later (after dropping
> them of course.) The company I worked for a long time ago (and I work
> for them again today :) had a consultant who also worked with the original
> company that owned/wrote the original ERwin product (when it was pretty
> much all that there was.) Hence we have grown an insane number of macros
> that deal with everything, even turning on and off triggers, custom
> triggers, constraints, indexes, everything. It was not easy, but my code
> is really really useful, especially during rapid development because I can
> modify all of the objects in just minutes.
> I occasionally use the base ERwin functionality but only when I am in a
> hurry to do something small. I used to try to lobby for changes, but
> after CA purchased it I just have never found the right way, and I have
> all of this code that works. The only real problem is that it is such a
> bother to use I cannot even get coworkers to use it, much less sharing it
> with the world. Perhaps I will do something like that and post it on my
> blog one day.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
>
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:zp6Oe.93559$G8.66384@.text.news.blueyonder.co.uk...
>|||I used ERWIN for a short time myself and I did like it up to a point.
Unfortunately, it doesn't support SQL Server 2000's declarative referential
integrity (DRI) and creates triggers to cascade updates and deletes to
related tables.
For this reason alone I stopped using ERWIN. I found that the triggers
remained after I altered table/column names or relationships, which caused
updates/deletes to fail until I realised what was happening.
I'm sure it's great for SQL 7, but it would have to be configured properly
to be worthwhile in SQL 2000. IMO.
Thanks. Dan.
"PJ6" <nobody@.nowhere.net> wrote in message
news:%23%23eGBBmpFHA.2888@.TK2MSFTNGP10.phx.gbl...
> I'm working with a developer that used ERWIN to create the database I have
> to interface with. At first glance I noticed that there are a ton of
> unecessary triggers, many of which look like they won't even be hit
because
> they're trying to detect and raise error messages for FK violations (when
> there are already FK contraints).
> I don't like what I'm seeing so far. Can anyone give me the lowdown about
> using this tool with SS2K?
> Paul
>|||I think so to. The basics we are both saying is that ERWin output sucks.
Cannot disagree with that, especially since I wrote my own script
generators, albeit in the ERwin tool :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:bdgOe.93743$G8.59922@.text.news.blueyonder.co.uk...
> Sounds to me like we're singing from the same hymm sheet. Just different
> parts of it. Currently I'm a script jockey, as you saw from my previous
> post I like to get things neat done one etc. as DBA for the company that I
> work for, I'm trying to tighten up the database structures to make sure
> that all foreign keys check constraints proper indexing etc, is in place.
> As I don't have an unlimited amount of time to get everything into place
> and working 100%, I took the decision early on that ERWin was as you say
> "it is such a bother to use". For documentation ERWin is brilliant for
> creating documentation, the diagrams are much better looking that the
> diagrams from SQL Server, so the company tend to use these. Also lesser
> experiences developers can use the tool for creating a basic table
> structure, brainstorming and stuff like that to get something off the
> ground. Once they're happy with the design, I have to perseude them to
> generate a script then go through it with a fine too comb to be sure that
> they've not missed anything. After they've finished, it's my turn and I
> spend ages giving the developer the third degree and adding about a
> million extra contraints and mocking their indexing ideas. However once
> corrected the database is extremely well designed. I just wish that
> ERWin could read in a script and learn the style that was used to create
> that script, then when using ERWin to generate a script it generates a
> script as close a possible to the original.
> I've waffled for too long on this now.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uAtpGSspFHA.3004@.TK2MSFTNGP15.phx.gbl...
>
sql
