mfarid1
asked on
Triggers created by ERWin
I am using ERWin to create tables and ERWin creates update and insert triggers, apparently to maintain integrity. Are they really needed to maintain integrity? Should I turn off the feature?
Also, here is some code from an insert trigger that ERWin created.
create trigger tI_tblAR on tblAR for INSERT as
/* ERwin Builtin Tue Jul 27 17:31:08 1999 */
/* INSERT trigger on tblAR */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Tue Jul 27 17:31:08 1999 */
/* tblMain R/97 tblAR ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(MainID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,tblMain
where
/* %JoinFKPK(inserted,tblMain ) */
inserted.MainID = tblMain.MainID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT tblAR because tblMain does not exist.'
goto error
end
end
/* ERwin Builtin Tue Jul 27 17:31:08 1999 */
/* tblARType R/79 tblAR ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(ARType)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,tblARType
where
/* %JoinFKPK(inserted,tblARTy pe) */
inserted.ARType = tblARType.ARType
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") */
1)What is update(MainID)
2)In the statement, 'from inserted, tblMain', what is inserted? Is it a table?
I can paste the rest of the code for the insert trigger and the update trigger if needed.
Musleh
Also, here is some code from an insert trigger that ERWin created.
create trigger tI_tblAR on tblAR for INSERT as
/* ERwin Builtin Tue Jul 27 17:31:08 1999 */
/* INSERT trigger on tblAR */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Tue Jul 27 17:31:08 1999 */
/* tblMain R/97 tblAR ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(MainID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,tblMain
where
/* %JoinFKPK(inserted,tblMain
inserted.MainID = tblMain.MainID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT tblAR because tblMain does not exist.'
goto error
end
end
/* ERwin Builtin Tue Jul 27 17:31:08 1999 */
/* tblARType R/79 tblAR ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(ARType)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,tblARType
where
/* %JoinFKPK(inserted,tblARTy
inserted.ARType = tblARType.ARType
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") */
1)What is update(MainID)
2)In the statement, 'from inserted, tblMain', what is inserted? Is it a table?
I can paste the rest of the code for the insert trigger and the update trigger if needed.
Musleh
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
mfarid1,
ERwin triggers are not needed to enforce referential integrity between the foreign key columns and the related primary key (or UNIQUE constraint) columns in another table.
Remember that constraints are checked prior to trigger execution. It means you must drop all constraints to see triggers work when these two mechanisms for enforcing business rules and data integrity exist.
Turn off the feature.
For more information, see "Triggers Compared to Other Data Integrity Methods" topic in BOL.
ERwin triggers are not needed to enforce referential integrity between the foreign key columns and the related primary key (or UNIQUE constraint) columns in another table.
Remember that constraints are checked prior to trigger execution. It means you must drop all constraints to see triggers work when these two mechanisms for enforcing business rules and data integrity exist.
Turn off the feature.
For more information, see "Triggers Compared to Other Data Integrity Methods" topic in BOL.
ASKER
Here are half the points for half the answer. I am giving the other half to gpbuenrostro
ASKER
Are the ERWin triggers really needed to maintain integrity? Should I turn off the feature? I already have constraints which I create.
Musleh