Re: How to create a before update trigger for 2 fields "expected_hash" and "identify_hash" (both allow null)

[Jan Marti]

Does somebody know how to solve my issue with a trigger?

1.) A table has among others 2 fields: 1x "identify_hash" and [recently] "expected_hash".


USE `pf_server`;

INSERT INTO `clientdb_mapping`
(
`expected_hash`,
`identify_hash`,
`db_name`,
`db_version`,
`allnodes`,
`licensedate`,
`licensedatenodes`
)
VALUES
(
NULL,
NULL,
'CUSTOMER_X_DATABASE', # Der Name der zuvor über das Skript erstellten Datenbank
'1.1.1',
'9999',
'2030-01-01',
'2030-01-01'
);


The above would be the "initial state" - both fields NULL...

2.) There should be a "before update" trigger, which prevents `identify_hash` from being unequal to `expected_hash` - according to the following logic:

- While `expected_hash` is NULL, `identify_hash` may also be NULL ("nullable").

- while `expected_hash` is NOT NULL, `identify_hash` may be NULL
--> While `expected_hash` is NULL, `identify_hash` must NOT be non-NULL

- While `expected_hash` AND `identify_hash` is NOT NULL, `identify_hash` must == `expected_hash`.