0709-0712

Previous Table of Contents Next

Page 709

Triggers, when used with sequences, also can be used to enforce primary key constraints. Listing 28.18 creates a trigger that ensures that new rows in the individual table are assigned unique primary key values. The trigger also ensures that the sequence is used, by overriding any value specified in an INSERT statement with the next value in the sequence.

Listing 28.18. This trigger enforces the primary key on the individual table.

 CREATE OR REPLACE TRIGGER indiv_get_key BEFORE INSERT ON individual FOR EACH ROW DECLARE     new_id NUMBER; BEGIN     SELECT individual_ids.nextval INTO new_id FROM dual;     :new.id := new_id; END indiv_get_key; 

Unfortunately, this trigger cannot coexist with the trigger in Listing 28.17. For each table, only one trigger can exist for each operation. Listing 28.19 demonstrates how the statements from the prior two listings can be implemented together, replacing the CHECK constraint on date_of_birth in the process.

Listing 28.19. You can use these triggers to enforce several constraints on the individual table.

 CREATE OR REPLACE TRIGGER indiv_ins_trg BEFORE INSERT ON individual FOR EACH ROW DECLARE     new_id NUMBER; BEGIN     IF ((:new.date_of_birth < TO_DATE('01JAN1847', `DDMONYYYY', `nls_date_language = American')) OR (:new.date_of_birth > sysdate)) THEN         RAISE_APPLICATION_ERROR(20001, `Invalid birth date.');     ELSE         SELECT individual_ids.nextval INTO new_id FROM dual;         :new.id := new_id;         :new.last_updt_user := user;         :new.last_updt_date := sysdate;     END IF; END indiv_ins_trig; CREATE OR REPLACE TRIGGER indiv_updt_trg BEFORE UPDATE ON individual FOR EACH ROW BEGIN     IF ((:new.date_of_birth < TO_DATE('01JAN1847', `DDMONYYYY', `nls_date_language = American'))         OR (:new.date_of_birth > sysdate)) THEN         RAISE_APPLICATION_ERROR(20001, `Invalid birth date.');     ELSE         :new.id := :old.id;         :new.last_updt_user := user;         :new.last_updt_date := sysdate;     END IF; END indiv_updt_trg; 

Page 710

In Listing 28.19, the PRIMARY KEY constraint is enforced for both inserts and updates. The UPDATE trigger completely prevents an ID from being changed, which might be unacceptable if this were not a generated key. The triggers also enforce a CHECK constraint on date_of_birth and the NOT NULL constraints on last_updt_user and last_updt_date.

You also can use triggers to enforce foreign key constraints. (Refer to Listing 28.9.) Both REFERENCES constraints in that example contain the ON DELETE CASCADE option. If the foreign keys' integrity constraints are enforced by the triggers in Listing 28.20, the REFERENCES column constraints can be removed.

Listing 28.20. You can use these triggers to enforce foreign key constraints.

 CREATE OR REPLACE TRIGGER indiv_del_trg BEFORE DELETE ON individual FOR EACH ROW BEGIN     DELETE FROM ind_co_rel WHERE individual_id = :old.id; END indiv_del_trg; CREATE OR REPLACE TRIGGER co_del_trg BEFORE DELETE ON company FOR EACH ROW BEGIN     DELETE FROM ind_co_rel WHERE company_id = :old.id; END co_del_trg; CREATE OR REPLACE TRIGGER ind_co_trg BEFORE INSERT OR UPDATE ON ind_co_rel FOR EACH ROW DECLARE     co_id    NUMBER;     indiv_id NUMBER; BEGIN     SELECT ID INTO co_id FROM company WHERE ID = :new.company_id;     SELECT ID INTO indiv_id FROM individual               WHERE ID = :new.individual_id;     EXCEPTION         WHEN OTHERS THEN              RAISE_APPLICATION_ERROR(20002, `Invalid id.'); END ind_co_trg; 

As these examples demonstrate , you can use triggers to perform the same tasks as table and column constraints. In many cases, it might be preferable to use triggers, because they are likely to provide better performance. This is particularly true in distributed environments, in which it might not be possible to enforce foreign key constraints at all.

When designing triggers, you should pay special attention to cascading triggers. Cascading occurs when a trigger on one table causes a trigger on another table to be fired . Codependencies, in particular, can be a problem.

Note that in Listing 28.20, the individual and company tables both have DELETE triggers that delete corresponding rows from ind_co_rel. For the sake of example, assume that ind_co_rel has a DELETE trigger that deletes corresponding rows in address_rel and phone_rel, and that individual and company also include these deletions in their DELETE triggers.

Page 711

Using triggers to maintain integrity can present numerous problems. If a relationship between an individual and a company is deleted, records in address_rel and phone_rel that are related to both should be deleted. Also, if an individual or a company is deleted entirely, all address_rel and phone_rel records related to the specific individual or company should be deleted. When an individual or a company is deleted, the ind_co_rel record is deleted, which causes its trigger to be fired, resulting in deletions from the address_rel and phone_rel tables. If these records also are to be deleted by the trigger that originally was fired by the deletion of the individual or company, the mutating table problem described earlier occurs. In this case, ind_co_rel probably should not have a DELETE trigger at all. Meaningless records in address_rel and phone_rel exist only until the corresponding individual or company is deleted. This is just one example of how cascading triggers can produce unexpected results.

Application and Performance Considerations

Regardless of how integrity is enforced in the database, the application must have knowledge of the constraints. The application must be able to submit transaction statements in the proper order, and it must know how to respond to exceptions resulting from integrity problems. This point is best illustrated through the use of an example. Suppose that the application needs to perform a single transaction based on the objects created in Listing 28.21.

Listing 28.21. These objects participate in a single transaction.

 CREATE SEQUENCE individual_ids     START WITH 1     INCREMENT BY 1     NOMAXVALUE; CREATE TABLE individual (      ID              NUMBER(10)                      CONSTRAINT indiv_pk          PRIMARY KEY     ,last_name       VARCHAR2(30)                      CONSTRAINT indiv_l_name      NOT NULL     ,first_name      VARCHAR2(30)                      CONSTRAINT indiv_f_name      NOT NULL     ,notes           VARCHAR2(255)     ,date_of_birth   DATE     ,last_updt_user  VARCHAR2(20)                      CONSTRAINT indiv_lu_id NOT NULL     ,last_updt_date  DATE                      CONSTRAINT indiv_lu_dt NOT NULL     ,CONSTRAINT indiv_chk_bday          CHECK (date_of_birth BETWEEN (TO_DATE('01JAN1847', `DDMONYYYY', `nls_date_language = American'))                 AND last_updt_date) ); 
 continues 

Page 712

Listing 28.21. continued

 CREATE SEQUENCE company_ids     START WITH 1     INCREMENT BY 1     NOMAXVALUE; CREATE TABLE company (      ID              NUMBER(10)                      CONSTRAINT company_pk PRIMARY KEY     ,name            VARCHAR2(30)                      CONSTRAINT co_name_u  UNIQUE                      CONSTRAINT co_name_nn NOT NULL     ,notes           VARCHAR2(255)     ,last_updt_user  VARCHAR2(20)                      CONSTRAINT co_lu_id   NOT NULL     ,last_updt_date  DATE                      CONSTRAINT co_lu_dt   NOT NULL ); CREATE TABLE ind_co_rel (      individual_id   NUMBER(10)     ,company_id      NUMBER(10)     ,title           VARCHAR2(80)     ,last_updt_user  VARCHAR2(20)                      CONSTRAINT indco_lu_id  NOT NULL                      EXCEPTIONS INTO ind_co_err_1     ,last_updt_date  DATE                      CONSTRAINT indco_lu_dt  NOT NULL                      EXCEPTIONS INTO ind_co_err_2     ,CONSTRAINT indco_ind_fk FOREIGN KEY (individual_id)           REFERENCES individual(ID)           ON DELETE CASCADE           EXCEPTIONS INTO ind_co_err_3     ,CONSTRAINT indco_co_fk  FOREIGN KEY (company_id)           REFERENCES company(ID)           ON DELETE CASCADE           EXCEPTIONS INTO ind_co_err_4     ,CONSTRAINT ind_co_rel_pk PRIMARY KEY(individual_id, company_id)           USING INDEX           TABLESPACE oracle_unleashed           STORAGE (INITIAL     10K                    NEXT        10K                    MAXEXTENTS  100                    PCTINCREASE  10)           EXCEPTIONS INTO ind_co_err_5 ); 

It would be useful if the application could insert an individual, a company, and a record relating the individual and company in one transaction. The foreign key constraints on indiv_co_rel dictate that this record must be inserted last. When designing a process to complete this transaction, you also should consider that the application will need to insert an individual and a company separately as well. Listing 28.22 provides three functions to accomplish these tasks.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net