× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Nick -

You can create column-level triggers with SQL, but I'm not sure that this
technique is supported on V4R5.
The example below was created on a V5R2 system.

Regards,
sjl

---------------------------------------------

-- ( Use RUNSQLSTM to run this...scan and replace YOURLIB with a valid
library name):
-- Review the COUNTERS table after running this to see the results...

Drop Trigger taxidtrg;
Drop Trigger taxidtrg2;
Drop Trigger ssntrg;
Drop Trigger ssntrg2;

Drop Table YOURLIB/address;
Drop Table YOURLIB/counters;

CREATE TABLE YOURLIB/ADDRESS
(
ABNO NUMERIC (8 , 0) NOT NULL WITH DEFAULT
, TAXID CHAR (20 ) NOT NULL WITH DEFAULT
, SSN CHAR (10 ) NOT NULL WITH DEFAULT
, NAME CHAR (30 ) NOT NULL WITH DEFAULT
) ;

CREATE TABLE YOURLIB/COUNTERS
(
KEYFLD NUMERIC (8 , 0) NOT NULL WITH DEFAULT
, ADDTAXID NUMERIC (8 , 0) NOT NULL WITH DEFAULT
, CHGTAXID NUMERIC (8 , 0) NOT NULL WITH DEFAULT
, DELTAXID NUMERIC (8 , 0) NOT NULL WITH DEFAULT
, ADDSSN NUMERIC (8 , 0) NOT NULL WITH DEFAULT
, CHGSSN NUMERIC (8 , 0) NOT NULL WITH DEFAULT
, DELSSN NUMERIC (8 , 0) NOT NULL WITH DEFAULT
) ;


CREATE TRIGGER TAXIDTRG
AFTER UPDATE OF TAXID ON YOURLIB/ADDRESS
REFERENCING OLD AS OL
NEW AS NW
FOR EACH ROW MODE DB2SQL
WHEN ( OL.TAXID <> NW.TAXID and NW.TAXID <> ' ')
BEGIN ATOMIC
UPDATE YOURLIB/COUNTERS
SET CHGTAXID = CHGTAXID + 1
WHERE KEYFLD = 1;
END;


CREATE TRIGGER TAXIDTRG2
AFTER INSERT on YOURLIB/ADDRESS
referencing new as NW
FOR EACH ROW MODE DB2SQL
WHEN ( NW.TAXID <> ' ')
BEGIN ATOMIC
UPDATE YOURLIB/COUNTERS
SET ADDTAXID = ADDTAXID + 1
WHERE KEYFLD = 1;
END;


CREATE TRIGGER SSNTRG
AFTER UPDATE OF SSN ON YOURLIB/ADDRESS
REFERENCING OLD AS OL
NEW AS NW
FOR EACH ROW MODE DB2SQL
WHEN ( OL.SSN <> NW.SSN and NW.SSN <> ' ')
BEGIN ATOMIC
UPDATE YOURLIB/COUNTERS
SET CHGSSN = CHGSSN + 1
WHERE KEYFLD = 1;
END;


CREATE TRIGGER SSNTRG2
AFTER INSERT ON YOURLIB/ADDRESS
REFERENCING NEW AS NW
FOR EACH ROW MODE DB2SQL
WHEN (NW.SSN <> ' ')
BEGIN ATOMIC
UPDATE YOURLIB/COUNTERS
SET ADDSSN = ADDSSN + 1
WHERE KEYFLD = 1;
END

INSERT INTO YOURLIB/COUNTERS
VALUES(1,0,0,0,0,0,0);

INSERT INTO YOURLIB/ADDRESS
VALUES(1,'XXXXXXXXXX','1234567890','NAME1');

INSERT INTO YOURLIB/ADDRESS
VALUES(2,'YYYYYYYYYY','0123456789','NAME2');

INSERT INTO YOURLIB/ADDRESS
VALUES(3,'ZZZZZZZZZZ','9876543210','NAME3');

UPDATE YOURLIB/ADDRESS SET NAME = RTRIM(NAME) CONCAT 'A'
WHERE ABNO = 1;

UPDATE YOURLIB/ADDRESS SET NAME = RTRIM(NAME) CONCAT 'B'
WHERE ABNO = 2;

UPDATE YOURLIB/ADDRESS SET TAXID = '0000000000'
WHERE ABNO = 1;

UPDATE YOURLIB/ADDRESS SET TAXID = 'AAAAAAAAAA'
WHERE ABNO = 2;

UPDATE YOURLIB/ADDRESS SET SSN = '9999999999'
WHERE ABNO = 1;

UPDATE YOURLIB/ADDRESS SET SSN = 'ZZZZZZZZZZ'
WHERE ABNO = 2;





As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.