| Subject | Script to add sequence |
|---|---|
| Author | Art McCabe |
| Post date | 2014-03-19T11:51:47Z |
Group,
I am trying to, in script:
1) add column to existing table
2) add generator
3) add trigger
4) update existing data
5) remove existing primary key
6) add new primary key
I have a script that works partially but I get some error when I try to run it as script.
I have modified it many time, to get it this far, I found that in order to update the data, i needed to have a default value set on the column. But with that set, the trigger does not work and the default will then cause a primary key violation.
I can remove the default, but if I do that in the same script i get errors.
the error I get is:
Engine Error (code = 335544351):
unsuccessful metadata update.
Local column ID doesn't have a default.
If I then disconnect and connect with a fresh connection, I can run the statement that failed and it works.
Here is the script that I am using. I dont think the commit statements are needed, but also dont think they hurt. the drop default is the like that i get the error on.
Any help in understanding what is happening is appreciated.
ALTER TABLE APHISTORY ADD ID bigint DEFAULT -1 NOT NULL;
COMMIT;
CREATE GENERATOR GEN_APHISTORY_ID;
SET TERM ^ ;
CREATE TRIGGER APHISTORY_ID FOR APHISTORY ACTIVE
BEFORE INSERT
AS BEGIN
END^
SET TERM ; ^
COMMIT;
UPDATE APHISTORY SET ID = GEN_ID(GEN_APHISTORY_ID, 1);
ALTER TABLE APHISTORY
DROP CONSTRAINT PK_APHISTORY;
ALTER TABLE APHISTORY
ADD CONSTRAINT PK_APHISTORY PRIMARY KEY(ID);
-- Now I need to remove the default.
ALTER TABLE APHISTORY ALTER ID DROP DEFAULT;
COMMIT;