Trigger or Identity

[lespaul]

I am using 'DeZign for Databases' (a modeling tool) to develop a relational database for our courthouse. We are using a DB2 UDB AS/400 database and the choices I have for the design tool are DB2 Universal DB7 or DB2 Universal DB8. One of the features to be implemented in the new schema is an autonumbering feature. In the modeling tool, I do not have the data type IDENTITY as a selection, but it is a choice in the iSeries Navigator:

CREATE TABLE CMPCHKINS(
SEQ INTEGER GENERATED ALWAYS AS IDENTITY (
START WITH 1 INCREMENT BY 1
NO MINVALUE NO MAXVALUE
NO CYCLE NO ORDER
CACHE 20))

So, in the modeling tool, I chose integers and enforced a before insert trigger:

CREATE TABLE tblPersonalInfo (
    PersonID INTEGER NOT NULL,
    FName VARCHAR(20) NOT NULL,
    LName VARCHAR(40) NOT NULL,
    MName VARCHAR(20),
    SSN CHAR(9),
    DOB DATE,
    DriverLicenseNo VARCHAR(40),
    DriverLicenseST CHAR(2),
    Height Ft INTEGER,
    Height In INTEGER,
    Sex CHAR(1),
    HairColor CHAR(25),
    EyeColor CHAR(25),
    Ethnicity CHAR(40),
    LanguageID INTEGER,
    PRIMARY KEY (PersonID)
);

CREATE TRIGGER Trigger_tblPersonalInfo_1
NO CASCADE BEFORE INSERT ON tblPersonalInfo
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
SET (N.PersonID) = (SELECT (MAX(PersonID)) + 1 FROM tblPersonalInfo);

Is there a preference for using IDENTITY over Triggers? Won't these both accomplish the same things?

Should I remove the triggers and tweak the generated SQL to include the IDENTITY?

Any advice?

Leslie

 

[CyberFan]

An IDENTITY will be somewhat more efficient, but you probably won't notice the difference.

Several things to consider here:

* Your tools should match each other. Either get a better/newer modelling tool and use IDENTITY, or use the your trigger on your current modeller.

* If you plan to upgrade at some point in the future, it may be easier if you utilize the IDENTITY field now.


Your call.

 

[lespaul]

I wrote the support team and got instructions on how to add IDENTITY support to the modeling tool! I'll be implementing it today!

Thanks for the info.



Leslie