MySQL Forums
Forum List  »  Triggers

Re: How to create a before update trigger for 2 fields "expected_hash" and "identify_hash" (both allow null)
Posted by: Jan Marti
Date: July 02, 2021 02:11AM

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`.

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.