Board index » delphi » How to assign field values in a trigger

How to assign field values in a trigger

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

 

Re:How to assign field values in a trigger


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

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

Re:How to assign field values in a trigger


Quote
> Where are your context variables (OLD & NEW)?  I don't see any.
> --
> Sergio Samayoa

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

Re:How to assign field values in a trigger


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/

Re:How to assign field values in a trigger


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 ; !!

Re:How to assign field values in a trigger


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

Re:How to assign field values in a trigger


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/

Re:How to assign field values in a trigger


Quote
"Sergio Samayoa" <serg...@terra.com.gt> wrote in message

news:3b190ddc_1@dnews...

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

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

Re:How to assign field values in a trigger


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

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

Re:How to assign field values in a trigger


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

Other Threads