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.
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.