Data validation at the database

Hi there informixer's, can you please advise as to how I can enforce the entry of today's date into a table field at the database on insert or update. Check constraints exclude the use of the system function TODAY. I tried using an UPDATE and INSERT trigger that allows comparison of the new incomming value with the TODAY function. The UPDATE trigger works fine, but the INSERT trigger works in every case accept where one of the fields is a serial field. The triggering SQL insert is rejected as required, but the seed/counter of the serial field is incremented. I don't want that to happen. Example below (*) Other info Dec apha Tru64 digital unix IDS version 7.24 database is logged I would be greatful for any advice. Cheers Zanne Fagerstrom IBM Global Services Australia (*) Database selected. SELECT UNIQUE TODAY FROM systables; (expression) 05/03/2003 1 row(s) retrieved. DROP TABLE test_dates;Table dropped. CREATE TABLE test_dates ( uniq_id SERIAL, test_date DATE);Table created. INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"05/03/2003");1 row(s) inserted. INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"05/03/2003");1 row(s) inserted. SELECT * FROM test_dates; uniq_id test_date 1 05/03/2003 2 05/03/2003 2 row(s) retrieved. DROP PROCEDURE force_today;Procedure dropped. CREATE PROCEDURE force_today( incomming_date DATE )IF incomming_date != TODAY THEN RAISE EXCEPTION -746, 0, 'Value of record date has to be TODAY';; END IF END PROCEDURE; Procedure created. ; CREATE TRIGGER ensure_today_insINSERT ON test_dates REFERENCING NEW AS new_rec FOR EACH ROW ( EXECUTE PROCEDURE force_today (new_rec.test_date) );Trigger created. CREATE TRIGGER ensure_today_updUPDATE ON test_dates REFERENCING NEW AS new_rec OLD AS old_rec FOR EACH ROW ( EXECUTE PROCEDURE force_today (new_rec.test_date) ) ;Trigger created. INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"01/03/2003");746: Value of record date has to be TODAY Error in line 1Near character position 65 INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"01/03/2003");746: Value of record date has to be TODAY Error in line 1Near character position 65 INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"05/03/2003");1 row(s) inserted. INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"05/03/2003");1 row(s) inserted. SELECT * FROM test_dates; uniq_id test_date 1 05/03/2003 2 05/03/2003 5 05/03/2003 6 05/03/2003 4 row(s) retrieved. Database closed.

Jack, I left out some details: I cannot control the application, only the database. There are many 'users' scripting loads adhoc and failing to apply any standards that may have been requested. The normal place to handle this would be in the application but I want to do it at the database because, if it can happen, it will happen, and especially given the volitility of the application changes. Thankyou for your response Regards Zanne Fagerstrom IBM Global Services Australia "Jack Parker" <vze2qjg5@verizon To: Zanne Fagerstrom/Australia/IBM@IBMAU .net> cc: Subject: Re: Data validation at the database [607] 07/03/2003 01:33 There are any number of ways. default the column to TODAY and don't enter it anywhere in your insert statement use the keyword TODAY when loading that column. cheers j. ----- Original Message ----- From: "Zanne Fager...." <zfagerst@au1.ibm.com> To: <ids@iiug.org> Sent: Wednesday, March 05, 2003 5:49 PM Subject: Data validation at the database [607] > > > > > Hi there informixer's, > can you please advise as to how I > can enforce the entry of today's date into a table field at the database > on insert or update. > > Check constraints exclude the use of the system function TODAY. > > I tried using an UPDATE and INSERT trigger that allows comparison of the > new incomming value with the TODAY function. > The UPDATE trigger works fine, but the INSERT trigger works in every case > accept where one of the fields is a serial field. > The triggering SQL insert is rejected as required, but the seed/counter of > the serial field is incremented. I don't want that to happen. > Example below (*) > > Other info > Dec apha Tru64 digital unix > IDS version 7.24 > database is logged > > I would be greatful for any advice. > > Cheers > > Zanne Fagerstrom > IBM Global Services Australia > > (*) > > Database selected. > > SELECT UNIQUE TODAY > FROM systables;> > (expression) > > 05/03/2003 > > 1 row(s) retrieved. > > DROP TABLE test_dates;> Table dropped. > > CREATE TABLE test_dates ( uniq_id SERIAL, test_date DATE);> Table created. > > INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"05/03/2003");> 1 row(s) inserted. > > INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"05/03/2003");> 1 row(s) inserted. > > SELECT * > FROM test_dates;> > uniq_id test_date > > 1 05/03/2003 > 2 05/03/2003 > > 2 row(s) retrieved. > > DROP PROCEDURE force_today;> Procedure dropped. > > CREATE PROCEDURE force_today( incomming_date DATE )> IF incomming_date != TODAY THEN > RAISE EXCEPTION -746, 0, 'Value of record date has to be TODAY';; > END IF > END PROCEDURE; > Procedure created. > > ; > > CREATE TRIGGER ensure_today_ins> INSERT ON test_dates > REFERENCING NEW AS new_rec > FOR EACH ROW > ( > EXECUTE PROCEDURE force_today (new_rec.test_date) > );> Trigger created. > > CREATE TRIGGER ensure_today_upd> UPDATE ON test_dates > REFERENCING NEW AS new_rec OLD AS old_rec > FOR EACH ROW > ( > EXECUTE PROCEDURE force_today (new_rec.test_date) > ) ;> Trigger created. > > INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"01/03/2003");> 746: Value of record date has to be TODAY > Error in line 1> Near character position 65 > > INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"01/03/2003");> 746: Value of record date has to be TODAY > Error in line 1> Near character position 65 > > INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"05/03/2003");> 1 row(s) inserted. > > INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"05/03/2003");> 1 row(s) inserted. > > SELECT * > FROM test_dates;> > uniq_id test_date > > 1 05/03/2003 > 2 05/03/2003 > 5 05/03/2003 > 6 05/03/2003 > > 4 row(s) retrieved. > > Database closed. > > > > > > >

Jack, more details I left out: I need to capture insert and update events and enforce TODAY's date. The addition of a new field of type date with default TODAY would cater for inserts but not updates. If an old value exists, updating would not impose TODAY's date. To summarise, I want the date to change to today's date when the record is touched in any way by insert or update. Thankyou for your interest. Regards Zanne Fagerstrom IBM Global Services ----- Forwarded by Zanne Fagerstrom/Australia/IBM on 07/03/2003 11:47 ----- "Jack Parker" <vze2qjg5@verizon To: Zanne Fagerstrom/Australia/IBM@IBMAU .net> cc: Subject: Re: Data validation at the database [607] 07/03/2003 01:33 There are any number of ways. default the column to TODAY and don't enter it anywhere in your insert statement use the keyword TODAY when loading that column. cheers j. ----- Original Message ----- From: "Zanne Fager...." <zfagerst@au1.ibm.com> To: <ids@iiug.org> Sent: Wednesday, March 05, 2003 5:49 PM Subject: Data validation at the database [607] > > > > > Hi there informixer's, > can you please advise as to how I > can enforce the entry of today's date into a table field at the database > on insert or update. > > Check constraints exclude the use of the system function TODAY. > > I tried using an UPDATE and INSERT trigger that allows comparison of the > new incomming value with the TODAY function. > The UPDATE trigger works fine, but the INSERT trigger works in every case > accept where one of the fields is a serial field. > The triggering SQL insert is rejected as required, but the seed/counter of > the serial field is incremented. I don't want that to happen. > Example below (*) > > Other info > Dec apha Tru64 digital unix > IDS version 7.24 > database is logged > > I would be greatful for any advice. > > Cheers > > Zanne Fagerstrom > IBM Global Services Australia > > (*) > > Database selected. > > SELECT UNIQUE TODAY > FROM systables;> > (expression) > > 05/03/2003 > > 1 row(s) retrieved. > > DROP TABLE test_dates;> Table dropped. > > CREATE TABLE test_dates ( uniq_id SERIAL, test_date DATE);> Table created. > > INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"05/03/2003");> 1 row(s) inserted. > > INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"05/03/2003");> 1 row(s) inserted. > > SELECT * > FROM test_dates;> > uniq_id test_date > > 1 05/03/2003 > 2 05/03/2003 > > 2 row(s) retrieved. > > DROP PROCEDURE force_today;> Procedure dropped. > > CREATE PROCEDURE force_today( incomming_date DATE )> IF incomming_date != TODAY THEN > RAISE EXCEPTION -746, 0, 'Value of record date has to be TODAY';; > END IF > END PROCEDURE; > Procedure created. > > ; > > CREATE TRIGGER ensure_today_ins> INSERT ON test_dates > REFERENCING NEW AS new_rec > FOR EACH ROW > ( > EXECUTE PROCEDURE force_today (new_rec.test_date) > );> Trigger created. > > CREATE TRIGGER ensure_today_upd> UPDATE ON test_dates > REFERENCING NEW AS new_rec OLD AS old_rec > FOR EACH ROW > ( > EXECUTE PROCEDURE force_today (new_rec.test_date) > ) ;> Trigger created. > > INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"01/03/2003");> 746: Value of record date has to be TODAY > Error in line 1> Near character position 65 > > INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"01/03/2003");> 746: Value of record date has to be TODAY > Error in line 1> Near character position 65 > > INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"05/03/2003");> 1 row(s) inserted. > > INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"05/03/2003");> 1 row(s) inserted. > > SELECT * > FROM test_dates;> > uniq_id test_date > > 1 05/03/2003 > 2 05/03/2003 > 5 05/03/2003 > 6 05/03/2003 > > 4 row(s) retrieved. > > Database closed. > > > > > > >

The serial value is not rolled back. To do that would serialize all inserts that use a serial column and significantly impact insert performance. To do what you are attempting, you would need to validate the data prior to issueing the insert. "Zanne Fager...." <zfagerst@au1.ibm To: ids@iiug.org .com> cc: Sent by: Subject: Re: Data validation at the database [623] forum.subscriber@ iiug.org 03/06/2003 09:47 PM Jack, more details I left out: I need to capture insert and update events and enforce TODAY's date. The addition of a new field of type date with default TODAY would cater for inserts but not updates. If an old value exists, updating would not impose TODAY's date. To summarise, I want the date to change to today's date when the record is touched in any way by insert or update. Thankyou for your interest. Regards Zanne Fagerstrom IBM Global Services ----- Forwarded by Zanne Fagerstrom/Australia/IBM on 07/03/2003 11:47 ----- "Jack Parker" <vze2qjg5@verizon To: Zanne Fagerstrom/Australia/IBM@IBMAU .net> cc: Subject: Re: Data validation at the database [607] 07/03/2003 01:33 There are any number of ways. default the column to TODAY and don't enter it anywhere in your insert statement use the keyword TODAY when loading that column. cheers j. ----- Original Message ----- From: "Zanne Fager...." <zfagerst@au1.ibm.com> To: <ids@iiug.org> Sent: Wednesday, March 05, 2003 5:49 PM Subject: Data validation at the database [607] > > > > > Hi there informixer's, > can you please advise as to how I > can enforce the entry of today's date into a table field at the database > on insert or update. > > Check constraints exclude the use of the system function TODAY. > > I tried using an UPDATE and INSERT trigger that allows comparison of the > new incomming value with the TODAY function. > The UPDATE trigger works fine, but the INSERT trigger works in every case > accept where one of the fields is a serial field. > The triggering SQL insert is rejected as required, but the seed/counter of > the serial field is incremented. I don't want that to happen. > Example below (*) > > Other info > Dec apha Tru64 digital unix > IDS version 7.24 > database is logged > > I would be greatful for any advice. > > Cheers > > Zanne Fagerstrom > IBM Global Services Australia > > (*) > > Database selected. > > SELECT UNIQUE TODAY > FROM systables;> > (expression) > > 05/03/2003 > > 1 row(s) retrieved. > > DROP TABLE test_dates;> Table dropped. > > CREATE TABLE test_dates ( uniq_id SERIAL, test_date DATE);> Table created. > > INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"05/03/2003");> 1 row(s) inserted. > > INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"05/03/2003");> 1 row(s) inserted. > > SELECT * > FROM test_dates;> > uniq_id test_date > > 1 05/03/2003 > 2 05/03/2003 > > 2 row(s) retrieved. > > DROP PROCEDURE force_today;> Procedure dropped. > > CREATE PROCEDURE force_today( incomming_date DATE )> IF incomming_date != TODAY THEN > RAISE EXCEPTION -746, 0, 'Value of record date has to be TODAY';; > END IF > END PROCEDURE; > Procedure created. > > ; > > CREATE TRIGGER ensure_today_ins> INSERT ON test_dates > REFERENCING NEW AS new_rec > FOR EACH ROW > ( > EXECUTE PROCEDURE force_today (new_rec.test_date) > );> Trigger created. > > CREATE TRIGGER ensure_today_upd> UPDATE ON test_dates > REFERENCING NEW AS new_rec OLD AS old_rec > FOR EACH ROW > ( > EXECUTE PROCEDURE force_today (new_rec.test_date) > ) ;> Trigger created. > > INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"01/03/2003");> 746: Value of record date has to be TODAY > Error in line 1> Near character position 65 > > INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"01/03/2003");> 746: Value of record date has to be TODAY > Error in line 1> Near character position 65 > > INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"05/03/2003");> 1 row(s) inserted. > > INSERT INTO test_dates (uniq_id,test_date) VALUES (0,"05/03/2003");> 1 row(s) inserted. > > SELECT * > FROM test_dates;> > uniq_id test_date > > 1 05/03/2003 > 2 05/03/2003 > 5 05/03/2003 > 6 05/03/2003 > > 4 row(s) retrieved. > > Database closed. > > > > > > >