SQL: No Identity feature workaround using triggers, Any Help?

Question

I'm a little rusty with my triggers and what not and am trying to figure out this problem for a class:

In a database TEST, tables do not have the option of the IDENTITY feature. In other words, when we insert a row into the table “Users”, we would like the primary key “UserID” to auto-increment. Please suggest a workaround to implement this feature without such a built-in functionality. (Hint: You may still use functions, stored procedures, sequences, triggers, etc)

Any takers on this ?

Thanks

Answer 1

You use a sequence, and it's very common with Oracle, which does not (or did not once, it may have changed) have identity columns. Since this is homework I'll let you figure out the rest from here.

Answer 2

Use an Int column for the table Primary Key called ID.

You can then use an instead of Insert Trigger, to populate/calculate the value to be inserted for ID.

The trigger will determine what the maximum existing ID is for the table in question (using select MAX ID from TableA) and then increment it by 1 for each record to be inserted.

If there are no records in the table then the ID value is 1.