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 IF (NEW.ID IS NULL) THEN NEW.ID = GEN_ID(GEN_APHISTORY_ID, 1); 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;
