I just tested my last change:
--#SET TERMINATOR !
CREATE TRIGGER EMPSCREDIT
AFTER INSERT ON EMP_SCREEN_EDIT
REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE reason VARCHAR(68);
DECLARE OUT_SQLCODE INTEGER;
CALL execute_immediate('select hiredate from
EMP_SCREEN_EDIT',OUT_SQLCODE)
;
SET reason
= CASE WHEN OUT_SQLCODE <> 0
THEN '292 INVALID HIREDATE'
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!
insert with wrong hiredate:
insert into EMP_SCREEN_EDIT
values(USER,CURRENT_TIMESTAMP,'000900','LENNY','D','GOLD','A01','99/27/1999',
'MANAGER',12, 'M','11/02/1953',140000.00,900.00,3000.00);
i expect - '292 INVALID HIREDATE'
but i got - sqlcode: -181
The string representation of a datetime value is out of range.
It din't work.???
Serge Rielau wrote:
>> I TRIED ONLY SP AND ALSO AN ERROR:
>>
>[quoted text clipped - 4 lines]
>> SQLSTATE=42703
>> sqlcode: -206
>Of course out_sqlcode is not defined.
>
>in your trigger this presumably looks like this:
>
>CREATE TRIGGER ....
> BEGIN ATOMIC
> DECLARE out_sqlcode INTEGER;
> CALL execute_immediate('....', out_sqlcode);
> END
>@[EMAIL PROTECTED]
>
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200807/1
|
