Link to home
Start Free TrialLog in
Avatar of mfarid1
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,tblARType) */
          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
Avatar of BennyBunny
BennyBunny

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mfarid1
mfarid1

ASKER

BennyBunny, thank you for your partial answer but my other questions were:

Are the ERWin triggers really needed to maintain integrity? Should I turn off the feature? I already have constraints which I create.

Musleh
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.
Avatar of mfarid1

ASKER

Here are half the points for half the answer. I am giving the other half to gpbuenrostro