Rob,
thanks for posting a full example for every one showing a simple trigger program. I know that DDS constraints have been mentioned, but could constraints do something like email validation?
CREATE TABLE MYLIB/EMAILADDR ( EMAIL_ADDRESS FOR COLUMN EMAIL00001 VARCHAR(256) CCSID 37 DEFAULT NULL );
CREATE TRIGGER MYLIB/EMAILCHECK BEFORE INSERT OR UPDATE OF EMAIL_ADDRESS ON MYLIB/EMAILADDR REFERENCING NEW AS N FOR EACH ROW MODE DB2ROW BEGIN DECLARE V_CHECK INTEGER ; DECLARE V_ERROR_MSG VARCHAR ( 256 ) ; SET V_CHECK = CASE WHEN LOCATE ( '.' , TRIM ( N . EMAIL_ADDRESS ) , LOCATE ( '@' , TRIM ( N . EMAIL_ADDRESS ) ) ) > 1 AND LOCATE ( ' ' , TRIM ( N . EMAIL_ADDRESS ) ) = 0 AND LOCATE ( '@' , TRIM ( N . EMAIL_ADDRESS ) ) > 1 AND LOCATE ( '@' , TRIM ( N . EMAIL_ADDRESS ) , LOCATE ( '@' , TRIM ( N . EMAIL_ADDRESS ) ) + 1 ) = 0 AND LENGTH ( TRIM ( TRANSLATE ( LEFT ( TRIM ( N . EMAIL_ADDRESS ) , CASE WHEN LOCATE ( '@' , N . EMAIL_ADDRESS ) = 0 THEN 1 ELSE LOCATE ( '@' , N . EMAIL_ADDRESS ) - 1 END ) , ' ' , '''abcdefghijklmnopqrstuvwxyzABCDECFGHIJKLMNOPQRSTUVWXYZ0123456789!#$%*/?|^{}`~&+-=_.' ) ) ) = 0 AND LENGTH ( TRIM ( TRANSLATE ( SUBSTR ( TRIM ( N . EMAIL_ADDRESS ) , CASE WHEN LOCATE ( '@' , TRIM ( N . EMAIL_ADDRESS ) ) = 0 THEN 1 ELSE LOCATE ( '@' , TRIM ( N . EMAIL_ADDRESS ) ) + 1 END , LENGTH ( TRIM ( N . EMAIL_ADDRESS ) ) - ( LOCATE ( '@' , TRIM ( N . EMAIL_ADDRESS ) ) ) ) , ' ' , '''abcdefghijklmnopqrstuvwxyzABCDECFGHIJKLMNOPQRSTUVWXYZ0123456789-.' ) ) ) = 0 AND LOCATE ( '.@' , TRIM ( N . EMAIL_ADDRESS ) ) = 0 AND LEFT ( TRIM ( N . EMAIL_ADDRESS ) , 1 ) <> '.' AND SUBSTR ( TRIM ( N . EMAIL_ADDRESS ) , LENGTH ( TRIM ( N . EMAIL_ADDRESS ) ) , 1 ) <> '.' AND LOCATE ( '-@' , TRIM ( N . EMAIL_ADDRESS ) ) = 0 AND LOCATE ( '@-' , TRIM ( N . EMAIL_ADDRESS ) ) = 0 AND LOCATE ( '..' , TRIM ( N . EMAIL_ADDRESS ) ) = 0 THEN 1 ELSE 0 END; IF SQLP_L2 . V_CHECK = 0 THEN SET V_ERROR_MSG = 'Not a valid email'; SIGNAL SQLSTATE VALUE 'SA001' SET MESSAGE_TEXT = SQLP_L2 . V_ERROR_MSG; END IF; END;
Insert Into MYLIB.EMAILADDR Values('qandjh2001@hotmail.');
results in SQL
SQL State: 09000Vendor Code: -723Message: [SQL0723] SQL trigger EMAILCHECK in E133372 failed with SQLCODE -438 SQLSTATE SA001. Cause . . . . . : An error has occurred in a triggered SQL statement in trigger EMAILCHECK in schema E133372. The SQLCODE is -438, the SQLSTATE is SA001, and the message is Not a valid email. Recovery . . . : Refer to the joblog for more information regarding the detected error. Correct the error and try the request again.
result in joblog
Message Not a valid email returned from SIGNAL, RESIGNAL, or RAISE_ERROR.SQL trigger EMAILCHECK in E133372 failed with SQLCODE -438 SQLSTATE SA001.Error occurred in trigger program.Error occurred in trigger program.SQL trigger EMAILCHECK in E133372 failed with SQLCODE -438 SQLSTATE SA001.
Jim
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Anyone using Triggers to enforce data validation & business rules?
From: rob@xxxxxxxxx
Date: Mon, 22 Sep 2014 07:47:14 -0400
Been awhile, but on your constraint error not only can you handle the code
generated for a constraint violation but you could interpret the
constraint name. Instead of using system generated constraint names you
could call it something like NoOrderHeaderForThisOrderLine. If
multilingual is a concern pull the message description from a message file
keyed by the constraint name. Or your other favored multilingual
technique.
Now's the time you may have to do it right, versus that old 1970's code
that assumed that every write error was a duplicate key error and then did
a chain/update.
midrange.com
