Page 1 of 1

[Solved] Alter Table T1 Alter Column "Date" set default

Posted: Fri May 10, 2013 7:43 pm
by F3K Total
Hello,
i wonder, if there is a possibility for setting another timestamp by default then

Code: Select all

CURRENT_TIMESTAMP
?
e.g.

Code: Select all

Alter Table T1 Alter Column "Date" set default CURRENT_TIMESTAMP - 1 DAY
I know, this does not work.
I'm using a splitted or a server Version of HSQL 2.2.8, so also Triggers are possible, but i don't know if neccessary and when, how to do it.
Can anybody help me?
And a further question is, if it would be possible to decide between different default values e.g. on the current time.
If it's before 12:00 AM then CURRENT_TIMESTAMP - 1 DAY
If it's after 12:00 AM then CURRENT_TIMESTAMP + 1 DAY
Rik

Re: Alter Table T1 Alter Column "Date" set default

Posted: Fri May 10, 2013 8:29 pm
by Villeroy
HSQL 1.8 can not calculate any time stamps except for the current time stamp.

I tried with HSQL 2.2.8 which has a DateAdd function:

Code: Select all

ALTER TABLE "T1" ALTER COLUMN "Date" SET DEFAULT DATEADD('day',-1,CURRENT_TIMESTAMP)
but it does not accept any function name after keyword DEFAULT.

Re: Alter Table T1 Alter Column "Date" set default

Posted: Sat May 11, 2013 8:27 am
by F3K Total
Good morning,
thank you for your reply Villeroy.
I'm not used to TRIGGERS but with following code, made from snippets, I'm close to a solution, 'cause it inserts the desired Date once, before the error cardinality violation
occurs.

Code: Select all

CREATE TRIGGER DAYBEFORETRIG AFTER INSERT ON "Testtable"
REFERENCING NEW ROW AS NEWROW
FOR EACH ROW
BEGIN ATOMIC
    SELECT DATEADD('dd',-1,CURRENT_DATE) INTO "Date" from "Testtable";
END
Any ideas?
R

Re: Alter Table T1 Alter Column "Date" set default

Posted: Sat May 11, 2013 4:14 pm
by Sliderule
Rik:

You said / asked:
Rik wrote:If it's before 12:00 AM then CURRENT_TIMESTAMP - 1 DAY
If it's after 12:00 AM then CURRENT_TIMESTAMP + 1 DAY
The above, makes no sense, at least, not to me. Since, 12:00 AM is MIDNIGHT ( put another way . . . 00:00:00 ) . . . it is NOT possible for it to be "before" that time.

I mention this, because, unless we can better understand exactly your needs, it is hard to provide a response. Perhaps, you can give several examples, to show us exactly what you want the database back-end to perform, so, someone might be able to help with a solutions.

Sliderule

Re: Alter Table T1 Alter Column "Date" set default

Posted: Sat May 11, 2013 6:46 pm
by F3K Total
Hello Sliderule,
I'm sorry, if we would use the 24 hour time, i mean 12 o'clock. Isn't it high noon?
The if-else question is secondary, what i first would like to do, is to set another timestamp/date then Current_Timestamp/Current_Date as default.
But if possible, i'd like to have a solution for this question:
Somebody wants to to set a Date as Work-beginning Date.
They have a DB to store datas of Phonecalls. If e.g. the first call is stored, with default current_timestamp, at 0.00 AM to 6.00 AM, it means, that the Guy startet working at 11:00 PM the day before. Now they like to have the Date of the day before as Work-beginning Date.
R

Re: Alter Table T1 Alter Column "Date" set default

Posted: Sat May 11, 2013 10:23 pm
by Sliderule
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:
  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.
  2. 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

Re: Alter Table T1 Alter Column "Date" set default

Posted: Sun May 12, 2013 12:09 am
by F3K Total
Hello Sliderule,
Sliderule wrote:While I think I know what you want . . . I must say, I am NOT 100% certain
You did get me!
This trigger does exactly what i wanted to have! Thank you very much.
Now it's possible for me to insert any date/timestamp depending on current values and other conditions into a DB.
e.g. a different start/end of a period, depending on someones age, sex or anything else.
Thanks again.
Rik

Re: [Solved] Alter Table T1 Alter Column "Date" set default

Posted: Sun May 12, 2013 9:54 pm
by F3K Total
Hello Sliderule,

i hope you don't mind if i add an extended question here.
The great trigger that you gave me, works on inserting a new row.
I now added one Column to the table, so that i have on column "CurrentDate" containing the current Timestamp and a second one "SpecialDate" containing the modified timestamp, like this:

Code: Select all

CREATE CACHED TABLE "Testtable" (
   ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL PRIMARY KEY,
   TEXT VARCHAR_IGNORECASE(50),
   "CurrentDate" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,"SpecialDate" TIMESTAMP,
   VALUE DECIMAL(10,2) DEFAULT 0.00);
and applied this trigger for testing

Code: Select all

CREATE TRIGGER DAYBEFORETRIG
AFTER INSERT ON "Testtable"
REFERENCING NEW ROW AS NEWROW
FOR EACH ROW
BEGIN ATOMIC
   IF HOUR(NEWROW."CurrentDate") <= 1 THEN
      UPDATE "Testtable" SET "SpecialDate" = DATEADD('dd', -1, "CurrentDate") WHERE ID = NEWROW.ID;
   ELSE
      UPDATE "Testtable" SET "SpecialDate" = DATEADD('dd', -2, "CurrentDate") WHERE ID = NEWROW.ID;
   END IF;
END
which is working very good on inserting a new row.

What i now would like to try is, if i change the timestamp (in "CurrentDate") manualy on an already exiting row, how does a second trigger on the same Table has to look like, that it updates "SpecialDate" Row in the same manner as the first trigger does?
I've tried it by changing your trigger from:

Code: Select all

AFTER INSERT ON "Testtable"
REFERENCING NEW ROW AS NEWROW...
to

Code: Select all

AFTER UPDATE ON "Testtable"
REFERENCING OLD ROW AS OLDROW ...
But it doesn't work.
Can you help me once again?
Rik

Re: [Solved] Alter Table T1 Alter Column "Date" set default

Posted: Sun May 12, 2013 10:46 pm
by Sliderule
Rik:

OK, how about trying the following BEFORE UPDATE Trigger:

Code: Select all

CREATE TRIGGER DAYBEFORETRIGUPDATE 
BEFORE UPDATE ON "Testtable" 
REFERENCING OLD ROW AS OLDROW NEW ROW AS NEWROW 

FOR EACH ROW WHEN (OLDROW."CurrentDate" <> NEWROW."CurrentDate") 
    BEGIN ATOMIC 
    IF HOUR(NEWROW."CurrentDate") <= 1 THEN 
       SET NEWROW."SpecialDate" = DATEADD('dd', -1, NEWROW."CurrentDate"); 
    ELSE 
       SET NEWROW."SpecialDate" = DATEADD('dd', -2, NEWROW."CurrentDate"); 
    END IF; 
END
Explanation 1: What the above does, is:
  1. Reset the calculated value for NEWROW."SpecialDate" . . . BUT . . . only when OLDROW."CurrentDate" <> NEWROW."CurrentDate" . . . <> means . . . NOT EQUAL TO . . . put another way, only when a change in the value of "CurrentDate" .
  2. Notice, that, since this is a BEFORE UPDATE statement . . . you do NOT perform an actual UPDATE, since, HSQL ( your database back-end ) will do it, automatically, BUT with the modified value for "SpecialDate", exactly as you defined it ( at least I think ).
Explanation 2: You may use the following Select statement, to 'document' the Triggers you have in place.

Code: Select all

Select 
   TRIGGER_NAME, 
   ACTION_TIMING, 
   EVENT_MANIPULATION, 
   EVENT_OBJECT_TABLE, 
   ACTION_ORDER, 
   ACTION_CONDITION, 
   ACTION_STATEMENT 
FROM INFORMATION_SCHEMA.TRIGGERS;
Sliderule

Re: [Solved] Alter Table T1 Alter Column "Date" set default

Posted: Sun May 12, 2013 11:10 pm
by F3K Total
Hello Sliderule,
wonderfull! The trigger works exactly as it should. Now it's not only possible for me to change datas on inserting new records, but also on updating existing records.
Sliderule wrote:Select
TRIGGER_NAME,
ACTION_TIMING,
EVENT_MANIPULATION,
EVENT_OBJECT_TABLE,
ACTION_ORDER,
ACTION_CONDITION,
ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS;
This is also a wonderfull help not to loose control over the triggers, or even better, i thought before, if that would be possible?
I'm near to say, with my small experience: These triggers are better then lots of macros, changing the DB'S data from outside.
I'll keep them save in mind and try to use them as often as possible.
Great Job!!!
Thank you again, Sliderule
Rik