Re: Anyone using Triggers to enforce data validation & business rules?

Please try the following in your own shop:
Start IBM i Navigator for Windows.
Expand your IBM i
Expand Databases
Select the database below it (almost always named the same as the IBM i
involved).
Off to the bottom right you will see Databases tasks.
Start "Run an SQL script"

For your first statement put the following:

create table yourlib.yourtable
(ActiveRecordCode char (1) not null,
constraint Cst_ActiveRecordCode_MustBeZ check (ActiveRecordCode='Z'));

Run that. After that is done run:

INSERT INTO yourlib.yourtable (ActiveRecordCode) VALUES('A');

You will get an error like this:
SQL State: 23513
Vendor Code: -545
Message: [SQL0545] INSERT, UPDATE, or MERGE not allowed by CHECK
constraint. Cause . . . . . : The value being inserted or updated does
not meet the criteria of CHECK constraint CST_ACTIVERECORDCODE_MUSTBEZ.
The operation is not allowed. Recovery . . . : Change the values being
inserted or updated so that the CHECK constraint is met. Otherwise, drop
the CHECK constraint CST_ACTIVERECORDCODE_MUSTBEZ.

Now, what possible use would be some mediator on this? This is not a
trigger. This is a constraint. How would your mediator even be activated
upon a check constraint? How would you possibly "grab the message and
forward it on up the stack."? And, if you could do so, what would then
appear the next time you run this insert using Run an SQL Script? Instead
of getting
SQL State: 23513
Vendor Code: -545
Message: [SQL0545] INSERT, UPDATE, or MERGE not allowed by CHECK
constraint. Cause . . . . . : The value being inserted or updated does
not meet the criteria of CHECK constraint CST_ACTIVERECORDCODE_MUSTBEZ.
The operation is not allowed. Recovery . . . : Change the values being
inserted or updated so that the CHECK constraint is met. Otherwise, drop
the CHECK constraint CST_ACTIVERECORDCODE_MUSTBEZ.

You would get, what? The following?
SQL State: ?????
Vendor Code: ???
Message: [CPF9898] ???



Rob Berendt