I'm hoping that someone is real clever. I have a table


CREATE TABLE "Sites"
(
"_fkCompanies" INTEGER,
"_SiteID" INTEGER,
"_SiteName" VARCHAR(30) COLLATE "ANSI_CI",
"_Switchboard" VARCHAR(25) COLLATE "ANSI_CI",
"_Fax" VARCHAR(25) COLLATE "ANSI_CI",
"_Address1" VARCHAR(35) COLLATE "ANSI_CI",
"_Address2" VARCHAR(35) COLLATE "ANSI_CI",
"_Town" VARCHAR(35) COLLATE "ANSI_CI",
"_County" VARCHAR(35) COLLATE "ANSI_CI",
"_Country" VARCHAR(35) COLLATE "ANSI_CI",
"_PostCode" VARCHAR(10) COLLATE "ANSI_CI",
"_Notes" CLOB COLLATE "ANSI_CI",
CONSTRAINT "PK" PRIMARY KEY ("_fkCompanies", "_SiteID")
)

I want to change _SiteID to GENERATED, the only problem is I want an autoinc for each company eg

_fkCompanies = 1
_SiteID 0 for the first, 1 for the second etc

_fkCompanies = 2
_SiteID 0 for the first, 1 for the second etc

Is this even possible?

Roy Lambert

Roy

> I want to change _SiteID to GENERATED, the only problem is I want an autoinc for each company eg
>
> _fkCompanies = 1
> _SiteID 0 for the first, 1 for the second etc
>
> _fkCompanies = 2
> _SiteID 0 for the first, 1 for the second etc

How about not using a generated value but something like

select max(_SiteID) from Sites where _fkCompanies = newRow._fkCompanies

and putting this into a trigger?

Uli

Uli

>How about not using a generated value but something like
>
>select max(_SiteID) from Sites where _fkCompanies = newRow._fkCompanies
>
>and putting this into a trigger?

That's one of the options (I've also thought of moving to a straight autoinc), but I always have difficulty in getting my brain round which trigger to use, especially in this case where the column is part of the primary key, and the other part is the column I want to use to determine the value.

I also already have logging triggers on this table and I'll want to disable them for the import so I'll have to have two triggers for the same event, and I'm not sure when each will fire.

My initial thought is BeforeInsert  or AfterInsert but I'm not sure if _fkCompanies is there at that point I'll have to experiment.

All thoughts welcome.

Roy Lambert

Roy,

> My initial thought is BeforeInsert  or AfterInsert but I'm not sure if _fkCompanies is there at that point I'll have to experiment.

I tried this with your table and this trigger works fine for me:

Create TRIGGER "SetSiteID" BEFORE INSERT
ON "Sites"
BEGIN
  DECLARE Result CURSOR FOR Stmt;
  DECLARE NewID INTEGER;
  PREPARE Stmt FROM
    'SELECT Coalesce(max(_SiteID),0) as MAX from Sites where
_fkCompanies = ?';
  OPEN Result using NewRow._fkCompanies;
  Fetch first from result(Max) into NewID;
  Close Result;
  SET NewRow._SiteID = NewID + 1;
END

Uli

Uli


Many thanks - saves me some coding.

Roy Lambert

Uli


As you say it works fine. I think its the BeforeInsert which my brain translates as "create a blank record ready to be populated" rather than bung the data I've got into the table so I never expect it to have any data to work on.

Roy Lambert

Roy,

> "create a blank record ready to be populated" rather than ...

That's actually what one would expect. I made some experiments some time
ago to be sure about this stuff.

Uli