Platform Portability Considerations

Database triggers can be used to achieve the same results as generated columns. Triggers are discussed in greater detail in Chapter 9, "User-Defined Functions and Triggers." This section presents an alternative to the generated column example show in Figure 3.5.

Figure 3.20 shows the alternate table creation script.

Figure 3.20. Table creation for an alternative to a generated column.

 CREATE TABLE payroll ( employee_id INT NOT NULL,   base_salary DOUBLE,   bonus DOUBLE,   commission DOUBLE,   total_pay DOUBLE ); 

The column total_pay needs to be generated based on the values in the base_salary, bonus, and commission columns. Figure 3.21 shows the two triggers required to support this.

Figure 3.21. Triggers for generated column logic.

 CREATE TRIGGER bi_genpayroll     NO CASCADE BEFORE INSERT ON payroll     REFERENCING NEW AS n     FOR EACH ROW MODE DB2SQL        SET n.total_pay=n.base_salary*(1+n.bonus) + n.commission; CREATE TRIGGER bu_genpayroll     NO CASCADE BEFORE UPDATE OF base_salary, bonus, commission ON payroll     REFERENCING NEW AS n     FOR EACH ROW MODE DB2SQL        SET n.total_pay=n.base_salary*(1+n.bonus) + n.commission;