drop in Oracle needs to check for associated sequences and triggers

[elpete]

Creating a table with an increments column in Oracle is tricky. Oracle doesn't have any idea of IDENTITY or AUTO_INCREMENT, so instead we use a NUMBER as the column type and create an associated sequence and trigger. This works, but the sequence and trigger are completely separate entities; they are not tied to the table. So when we drop the table, the sequence and trigger stay around.

We can drop the sequence and triggers using the same naming convention as when we create them. Unfortunately, SchemaBuilder does not know if we created the table using an increments column when calling drop.

So, I have two ideas on fixing this.

1. Require the Oracle developer to drop these items manually: queryExecute( "DROP SEQUENCE SEQ_#tableName#" ) and queryExecute( "DROP TRIGGER TRG_#tableName#" ). This is the most straightforward as it requires no extra code in SchemaBuilder. It is not as nice since the tableName needs to be repeated.

2. Check if a sequence and/or a trigger exists using the naming convention and drop it if it does. This takes out the repeating of tableName and matches the behavior of using increments. It also could be a little unexpected. (Not totally unexpected since the user is using SchemaBuilder in the first place.)

@murpg Your thoughts?

[murpg]

Eric, we can enforce what goes in. We cannot force them to drop dependencies. So option 2.

…

On Mon, Jul 17, 2023, 7:02 PM Eric Peterson ***@***.***> wrote: Creating a table with an increments column in Oracle is tricky. Oracle doesn't have any idea of IDENTITY or AUTO_INCREMENT, so instead we use a NUMBER as the column type and create an associated sequence and trigger. This works, but the sequence and trigger are completely separate entities; they are not tied to the table. So when we drop the table, the sequence and trigger stay around. We can drop the sequence and triggers using the same naming convention as when we create them. Unfortunately, SchemaBuilder does not know if we created the table using an increments column when calling drop. So, I have two ideas on fixing this. 1. Require the Oracle developer to drop these items manually: queryExecute( "DROP SEQUENCE SEQ_#tableName#" ) and queryExecute( "DROP TRIGGER TRG_#tableName#" ). This is the most straightforward as it requires no extra code in SchemaBuilder. It is not as nice since the tableName needs to be repeated. 2. Check if a sequence and/or a trigger exists using the naming convention and drop it if it does. This takes out the repeating of tableName and matches the behavior of using increments. It also could be a little unexpected. (Not totally unexpected since the user is using SchemaBuilder in the first place.) @murpg <https://github.com/murpg> Your thoughts? — Reply to this email directly, view it on GitHub <#262>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEBUFDCPPMHAFJFYS6MXQLXQXAAPANCNFSM6AAAAAA2NSSUZA> . You are receiving this because you were mentioned.Message ID: ***@***.***>