lenygold via DBMonster.com wrote:
> I have an edit trigger:
>
> --#SET TERMINATOR !
> CREATE TRIGGER EMPSCREDIT
> NO CASCADE BEFORE INSERT ON EMP_SCREEN_EDIT
> REFERENCING NEW AS n
> FOR EACH ROW MODE DB2SQL
> BEGIN ATOMIC
> DECLARE reason VARCHAR(68);
> SET reason
> = CASE WHEN LENGTH(RTRIM(TRANSLATE(EMPNO, '*', ' 0123456789'))) <>
0
> THEN '130 INVALID EMP NUMBER - MUST BE NUMERIC '
> WHEN FIRSTNME <= ' '
> THEN '146 FIRSTNME IS MISSED'
> WHEN LENGTH(RTRIM(TRANSLATE(FIRSTNME, '*', '
> ABCDEFGHIGKLMNOPQRSTUVWXYZ'))) <> 0
> THEN '146 INVALID FIRSTNME MUST BE ALPHABETIC'
> WHEN LENGTH(RTRIM(TRANSLATE(MIDINIT, '*', '
> ABCDEFGHIGKLMNOPQRSTUVWXYZ'))) <> 0
> THEN '154 INVALID MIDINIT MUST BE SPACE OR ALPHABETIC'
> WHEN LASTNAME <= ' '
> THEN '165 LASTNAME IS MISSED'
> WHEN LENGTH(RTRIM(TRANSLATE(FIRSTNME, '*', '
> ABCDEFGHIGKLMNOPQRSTUVWXYZ'))) <> 0
> THEN '165 INVALID LASTNAME MUST BE ALPHABETIC'
> WHEN WORKDEPT NOT IN
('A01','BO1','C01','D11','D21','E01','E11',
> 'E21')
> THEN '177 INVALID DEPATRMENT, VALID -->
A01,B01,C01,D11,D21,E01,
> E11,E21'
> WHEN JOB NOT IN('ANALYST ','CLERK
','DESIGNER','FEILDREP',
> 'MANAGER ‘OPERATOR','PRES ','SALESREP')
> THEN '328 INVALID JOB DESCRIPTION'
> WHEN EDLEVEL NOT IN(12,14,15,16,17,18,19,20)
> THEN '345 INVALID EDUCATION LEVEL'
> WHEN *** NOT IN ('M','F')
> THEN '355 INVALID GENDER'
> WHEN SALARY = 0
> THEN '422 INVALID SALARY, MUST BE GREATER THAN ZERO'
> WHEN HIREDATE <= BIRTHDATE
> THEN '292 HIREDATE MUST GREATER THAN BITHDATE'
> WHEN YEAR(HIREDATE) - YEAR(BIRTHDATE) < 18
> THEN '292 HIRED EMPLOYEE MUST 18 OR OLDER'
> ELSE NULL END;
> IF reason IS NOT NULL THEN
> SIGNAL SQLSTATE '7500S' (reason);
> END IF;
> END!
>
> Also there are date fields which are not part of check in edit.
> When date field is wrong system is genereting message:
>
> The syntax of the string representation of a datetime value is incorrect
> sqlcode: -180 .
>
> Is it possible to process this error code in the trigger and populate
reason
> field.i
>
In DB2 for LUW not directly. To do things like condition handling inside
of a trigger push the logic into a stored procedure and CALL that.
The SQL Procedure has the full power of SQL PL at its disposal.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
|
