On Jul 14, 12:07 am, "lenygold via DBMonster.com" <u41482@[EMAIL PROTECTED]
> wrote:
> I am currently developing trigger based screen edit fro several 100
screens
> in COBOL/CICS
> EVIRONMENT.
> For every screen i have a corresponding DB2 TABLE and an edit trigger:
> for example:
> screen:
>
> MAP1 ABC INTERNATIONAL CORT****AION
> 07/11/2007
> PESONNEL DEPARTMNET
>
> EMPLOYEES PERSONAL INFORMATION
>
> EMPNO: 000010 FIRST NAME: CHRISTINE MIDINIT: I
LASTNAME:
> GOLDENTAYER WORK DEPARTMENT: A01
>
> DATE OF HIRE: 1995-01-01 EDUCATION LVL: 18 ***: F DATE OF
BIRTH:
> 1954-03-24
>
> SALARY: 95270.00 ENDYEAR_BONUS: 5000.00 COMISSION: 12000.00
>
> edit table:
> CREATE TABLE EMP_SCREEN_EDIT
> (USER_ID CHAR(20) NOT NULL
> ,EDIT_DATE TIMESTAMP NOT NULL
> ,EMPNO CHARACTER (00006) NOT NULL
> ,FIRSTNME VARCHAR (00012) NOT NULL
> ,MIDINIT CHARACTER (00001) NOT NULL
> ,LASTNAME VARCHAR (00015) NOT NULL
> ,WORKDEPT CHARACTER (00003)
> ,HIREDATE DATE
> ,JOB CHARACTER (00008)
> ,EDLEVEL SMALLINT NOT NULL
> ,*** CHARACTER (00001)
> ,BIRTHDATE DATE
> ,SALARY DECIMAL (09,02)
> ,BONUS DECIMAL (09,02)
> ,COMM DECIMAL (09,02));
>
> edit trigger not yet all fields included:
>
> --#SET TERMINATOR !
> CREATE TRIGGER EMPSCREEN
> AFTER 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'
> ELSE NULL END;
> IF reason IS NOT NULL THEN
> SIGNAL SQLSTATE '7500S' (reason);
> END IF;
> END!
>
> it working fine:
> insert into EMP_SCREEN_EDIT
>
values(USER,CURRENT_TIMESTAMP,'000900','LENNY','D','GOLD','Z01','06/02/1993',
> 'MANAGER',16, 'M','06/02/1953',40200.00,900.00,3000.00);
>
> "177 INVALID DEPATRMENT, VALID --> A01,B01,C01,D11,D21,E01,E11,E21
".
> SQLCODE +438,-438;
>
> But how to avoid create 2nd Trigger for Screen Update.
> At work i have DB2 OS390 V8.
> I don't have MERGE STATEMENT, and I don't have INSTEAD OF TRIGGERS.
> Any Ideas?
I don't think you can avoid that since triggers are fired either after
update or insert. Merge wont do you any good and instead of triggers
only works for views.
Is there some specific reason that you don't want to use check
constraints and foreign keys for this purpose?
/Lennart
|
