I am trying to write a trigger which concatenates string fields into another
string field to create a string time from string hours, mnutes and am/pm,
then to cast the resultant string time field into a time field. IB returns
an error Unknown column (-206). Here is my trigger declaration.

SET TERM !!
CREATE TRIGGER UPDATE_CANTPLAY_TIME FOR CANTPLAY
AFTER UPDATE AS
BEGIN
STRINGTIME = BADHOUR + ':' + BADMINUTE + ' ' + AMPM;
BADTIME = CAST(STRINGTIME AS TIME);
END !!
SET TERM ; !!

TIA for any help.
Mark Horrocks

Where are your context variables (OLD & NEW)?  I don't see any.

--
Sergio Samayoa
Lgica Software
http://www.geocities.com/logicasw/

Hi Sergio, thank for the response.
I'm a trigger newbie. I tried playing with a variety of combinations of
new.fieldname but still couldn't get it to work. The IB help has nothing at
all about how to assign a different field in the same table.

Mark Horrocks

You should read "SQL Reference" and "Developers Guide" manuals (you can
download from Borland's site or www.ibphoenix.com).

You can change NEW values on BEFORE INSERT or BEFORE UPDATE. Any change to
NEW on AFTER triggers doesn't have any efect over the table.

--
Sergio Samayoa
Lgica Software
http://www.geocities.com/logicasw/

Mark -

This is pretty close to what you want.

Note the || (double-bars) used to concat strings, rather than + (plus sign)

SET TERM !!
CREATE TRIGGER UPDATE_CANTPLAY_TIME FOR CANTPLAY
before UPDATE AS
  declare variable stringtime varchar(11);
BEGIN
  STRINGTIME = new.BADHOUR || ':' || new.BADMINUTE || ' ' || new.AMPM;
  new.BADTIME = CAST(STRINGTIME AS TIME);
END !!
SET TERM ; !!

I have found that even though I can enter 7:00 PM directly into a time field
in IB Console (it's changed to 19:00:00:00), if I cast('7:00 PM' as time), I
get conversion error string '7:00 PM'. With the trigger definition below, I
get conversion error string '' when I run
Update cantplay
set badhour = '7' where teamno = 686

(existing badhour = '9', badminute = '00' and ampm = 'PM')

Here is the complete metadata for my table CantPlay. Any help appreciated.

CREATE TABLE "CANTPLAY"
(
  "KEYNO" "DKEYFIELD",
  "TEAMNO" "DKEYFIELD",
  "BADTIME" TIME,
  "STRINGTIME" VARCHAR(11),
  "BADHOUR" VARCHAR(2),
  "BADMINUTE" VARCHAR(2),
  "AMPM" VARCHAR(2),
  "CID" "DLOOKUP",
 PRIMARY KEY ("KEYNO")
);
ALTER TABLE "CANTPLAY" ADD CONSTRAINT "CANTPLAY_TEAMNO_FK" FOREIGN KEY
("TEAMNO") REFERENCES TEAMS ("TEAMNO") ON UPDATE CASCADE ON DELETE CASCADE;
SET TERM ^ ;

/* Triggers only will work for SQL triggers */

CREATE TRIGGER "SET_KEYNO" FOR "CANTPLAY"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
NEW.KEYNO = GEN_ID(ID_GEN, 1);
END
 ^
CREATE TRIGGER "UPDATE_CANTPLAY_TIME" FOR "CANTPLAY"
ACTIVE BEFORE UPDATE POSITION 0
AS
  declare variable charbadhour char(2);
  declare variable numbadhour integer;
  declare variable charstringtime char(11);
BEGIN
  numbadhour = cast(new.badhour as numeric);
  if (new.AMPM = 'PM') then
      numbadhour = numbadhour + 12;
  if (numbadhour = 24) then
      numbadhour = 0;
  charbadhour = cast(numbadhour as char);
  if (numbadhour < 10) then
      charbadhour = '0' || charbadhour;
  new.STRINGTIME = new.BADHOUR || ':' || new.BADMINUTE || ' ' || new.AMPM;
  charstringtime = charbadhour || ':' || new.BADMINUTE || ':' || '00' || '.'
|| '00';
  new.BADTIME = CAST(charstringtime AS TIME);
END

Check AMPM field and, if PM, add 12 to hour. Dont include AMPM field on
string.

--
Sergio Samayoa
Lgica Software
http://www.geocities.com/logicasw/

news:3b190ddc_1@dnews...

Thanks Sergio,

This isn't the problem.
If I type this :-
new.stringtime = cast(19 as character);
I get conversion error from string ''.
Further, cast(19 as varying character), results in unknown token varying.
I cannot cast a number to a character.

Mark Horrocks

IB does automatic conversion between data types. Try it without cast.

--
Sergio Samayoa
Lgica Software
http://www.geocities.com/logicasw/

Perfect, thanks Segio.

Just in case anybody is interested, here is my successful trigger :-

CREATE TRIGGER "UPDATE_CANTPLAY_TIME" FOR "CANTPLAY"
ACTIVE BEFORE UPDATE POSITION 0
AS
  declare variable charbadhour char(2);
  declare variable numbadhour integer;
  declare variable charstringtime char(11);
BEGIN
  numbadhour = new.badhour;
  if (new.AMPM = 'PM') then
      numbadhour = numbadhour + 12;
  if (numbadhour = 24) then
      numbadhour = 0;
  charbadhour = numbadhour;
  if (numbadhour < 10) then
      charbadhour = '0' || charbadhour;
  new.STRINGTIME = new.BADHOUR || ':' || new.BADMINUTE || ' ' || new.AMPM;
  charstringtime = charbadhour || ':' || new.BADMINUTE || ':' || '00' || '.'
|| '00';
  new.BADTIME = charstringtime;
END