Tim

I tried to alter a column, screwed up on the defaults (I left them as integer) and I'm now getting this error

ElevateDB Error #700 An error was found in the trigger at line 151 and column 6 (ElevateDB Error #401 The column _WeeksBetweenContacts does not exist in the table Contacts)

I have two problems. 1) The column does exist and 2) event though EDBManager positions the cursor at 151x6 I can only count 5 rows


---------------------------------------- alter sql ----------------------------------------------------------------------------
ALTER TABLE "Contacts"
RENAME COLUMN "_WeeksBetweenContacts" TO "_IntervalContacts",
ALTER COLUMN "_IntervalContacts" AS VARCHAR(3) COLLATE "ANSI" DEFAULT '1m',
RENAME COLUMN "_DaysBetweenAttempts" TO "_IntervalAttempts",
ALTER COLUMN "_IntervalAttempts" AS VARCHAR(3) COLLATE "ANSI" DEFAULT '1d'


----------------------------------- Tim's nifty table reverse engineer --------------------------------------------------

CREATE TABLE "Contacts"
(
"_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"_Title" VARCHAR(4) COLLATE "ANSI_CI",
"_Forename" VARCHAR(15) COLLATE "ANSI_CI",
"_Surname" VARCHAR(30) COLLATE "ANSI_CI",
"_Soundex" VARCHAR(5) 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",
"_HomePhone" VARCHAR(25) COLLATE "ANSI_CI",
"_LastDPADate" DATE,
"_LastDPACheckBy" VARCHAR(30) COLLATE "ANSI_CI",
"_LastDPAAccessRequest" DATE,
"_LastDPASupplied" DATE,
"_Mobile" VARCHAR(25) COLLATE "ANSI_CI",
"_HomeEmail" VARCHAR(60) COLLATE "ANSI_CI",
"_LastRecruited" DATE,
"_LatestCVDate" DATE,
"_Placed" DATE,
"_fkJobCodes" VARCHAR(10) COLLATE "ANSI_CI",
"_fkMarkets" VARCHAR(10) COLLATE "ANSI_CI",
"_RecruitedBy" VARCHAR(3) COLLATE "ANSI_CI",
"_WeeksBetweenContacts" INTEGER DEFAULT 12, <<<<<<<<<<<<<<<<<<<<<<<
"_DaysBetweenAttempts" INTEGER DEFAULT 1, <<<<<<<<<<<<<<<<<<<<<<<<<
"_ModDate" DATE,
"_Status" VARCHAR(10) COLLATE "ANSI_CI",
"_DoB" DATE,
"_YoB" SMALLINT,
"_LastCallBy" VARCHAR(3) COLLATE "ANSI_CI",
"_Source" VARCHAR(20) COLLATE "ANSI_CI",
"_fkStaff_LTC" VARCHAR(5) COLLATE "ANSI_CI",
"_LTCLastAttempt" DATE,
"_PNotes" VARCHAR(35) COLLATE "ANSI_CI",
"_NoMailShots" BOOLEAN DEFAULT FALSE ,
"_LatestCVType" VARCHAR(10) COLLATE "ANSI_CI",
"_LTCAlarm" TIMESTAMP,
"_LastCall" DATE,
"_Created" DATE DEFAULT CURRENT_DATE,
"_Partner" VARCHAR(60) COLLATE "ANSI_CI",
"_HiringManager" BOOLEAN DEFAULT FALSE ,
"_NoticePeriod" VARCHAR(10) COLLATE "ANSI_CI",
"_BaseSalary" VARCHAR(15) COLLATE "ANSI_CI",
"_SalaryDate" DATE,
"_SalaryWanted" VARCHAR(15) COLLATE "ANSI_CI",
"_WantedDate" DATE,
"_NoEshots" BOOLEAN DEFAULT FALSE ,
"_PackageInfo" CLOB COLLATE "ANSI_CI",
"_Links" CLOB COLLATE "ANSI_CI",
"_Notes" CLOB COLLATE "ANSI_CI",
"_Skills" CLOB COLLATE "ANSI_CI",
"_OtherEddresses" CLOB COLLATE "ANSI_CI",
"_DPAHistory" CLOB COLLATE "ANSI_CI",
"_Attributes" CLOB COLLATE "ANSI_CI",
"_UserFlags" CLOB COLLATE "ANSI_CI",
"_LatestCV" BLOB,
"_SellingPoints" CLOB COLLATE "ANSI_CI",
"_OtherPhoneNumbers" CLOB COLLATE "ANSI_CI",
"_Referrals" CLOB COLLATE "ANSI_CI",
"_References" CLOB COLLATE "ANSI_CI",
CONSTRAINT "PK" PRIMARY KEY ("_ID")
)
VERSION 1
ENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768

Roy Lambert

Tim

I think I found the root cause

-----------------------------------------------------------------------------------------------------------------------------------------------------------
[Window Title]
Error

[Content]
The following error occurred with the execution of the SQL for the last operation:

ElevateDB Error #201 The column _WeeksBetweenContacts in the table Contacts cannot be dropped because it is still referenced by the UPDATE trigger ContactsAfterUpdate in the table Contacts

Would you like to copy the SQL to a new SQL editor window so that you can correct the problem and attempt to execute the SQL again ?

[Yes] [No]
-----------------------------------------------------------------------------------------------------------------------------------------------------------

This one makes sense. I forgot I had the columns in a trigger Is there any easy way of identifying all triggers a column is referenced in? Or is it just grind through them all?

Roy Lambert

Roy,

<< This one makes sense. I forgot I had the columns in a trigger Is
there any easy way of identifying all triggers a column is referenced in? Or
is it just grind through them all? >>

I'll be adding support for renaming columns in triggers when the source
columns are renamed shortly.

As for your question, the quick answer is no, but adding dependency
information to the catalog tables is also coming soon - the
reverse-engineering needs it.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>As for your question, the quick answer is no, but adding dependency
>information to the catalog tables is also coming soon - the
>reverse-engineering needs it.

OK, gets shovel out and starts to dig <groan>

Roy Lambert