While I think I know what you want . . . I must say, I am NOT 100% certain.
Let me tell you what I did . . . I 'tested' this using HSQL 2.2.8 as my database backend.
I created a 'test' table as follows:
Code: Select all
CREATE CACHED TABLE "Testtable" (
ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1) NOT NULL PRIMARY KEY,
TEXT VARCHAR_IGNORECASE(50),
"Date" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
VALUE DECIMAL(10,2) DEFAULT 0.00);
I created a TRIGGER as below:
Code: Select all
CREATE TRIGGER DAYBEFORETRIG
AFTER INSERT ON "Testtable"
REFERENCING NEW ROW AS NEWROW
FOR EACH ROW
BEGIN ATOMIC
IF HOUR(NEWROW."Date") <= 11 THEN
UPDATE "Testtable" SET "Date" = DATEADD('dd', -1, "Date") WHERE ID = NEWROW.ID;
END IF;
END
Explanation 1:
- The TRIGGER is executed, AFTER the INSERT to the table. The reason for this is, I think you may want to 'adjust' the value for the field "Date" . . . depending on the time of day.
- The value for the table field "Date" ( really a timestamp ) only is changed to the prior calendar day ( really an UPDATE ), when, HOUR(NEWROW."Date") is less than or equal to 11 ( 11 o'clock in the morning . . . so . . . until 11:59:59 ).
In my 'testing', with the following INSERT statements . . . AND . . . run on my computer with a computer date/time of
2013-05-11 10:23:15' ( notice this is BEFORE 12:00 NOON
):
Code: Select all
INSERT INTO "Testtable" (TEXT, "Date", VALUE) VALUES('Hello World', CURRENT_TIMESTAMP, 20.2);
INSERT INTO "Testtable" (TEXT, VALUE) VALUES('Hello World', 120.2);
INSERT INTO "Testtable" (TEXT, "Date", VALUE) VALUES('Hello World', {TS '2013-05-15 15:23:00'}, 320.2);
The resulting values were:
Code: Select all
ID TEXT Date VALUE
-- ----------- ----------------------- ------
1 Hello World 2013-05-10 10:23:15.00 20.20
2 Hello World 2013-05-10 10:23:15.00 120.20
3 Hello World 2013-05-15 15:23:00.0 320.20
Explanation 2: Notice how, the DATE was changed to the prior date ( NOT an input date of
2013-05-11, but rather,
2013-05-10 ), when the TIME was BEFORE 12:00 NOON . . . however, when the TIME was AFTER 12:00 NOON, the date was NOT changed ( the third record inserted, with a HARD-CODED date ). So, you can 'modify' the
UPDATE statement in the TRIGGER, to meet your specific requirements ( use of PRIMARY KEY to identify ( database pun intended ) the row just INSERTED ).
Now, I am hoping this will give you a place to start, so you can 'fine-tune' the TRIGGER to you exact needs.
Please be sure to let me /us know if this helps, and, best of luck.
Sliderule