On Wed, 19 Mar 2014 04:51:47 -0700 (PDT), Art McCabe <artmccabe@...>
wrote:

> 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.

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

You need to add a COMMIT between the UPDATE and the ALTER statement here.
You cannot use a table in DML and alter it with DDL in the same
transaction.

Mark