Using a Foreign Members Table with Oracle or PostgreSQL

Lyris ListManager requires that the column that stores the member ID be unique and auto-numbering. For Oracle this is implemented as a trigger against a sequence table; for Postgres, it is a default rule on the column that accesses the sequence table. Note when using a foreign members table with Oracle it has to be the same as the ListManager schema.

If you use a foreign member table, you may need to add the sequence to your member ID column yourself and make sure the next value is automatically populated. You need only add the sequence yourself if the existing ID field has no other means of getting a default unique value during an insert.

Note: This process is only necessary if the existing ID field has no other means of getting a default unique value during an insert. It is probable that the foreign member table already has an ID field, and some means for generating unique values for that field.

Creating a Sequence Table for Oracle

This example presumes you want to use a foreign members table with the name "LOCAL_MEMBERS", using the column "ID" as the Member ID column.

1. Find out the maximum number already in the ID column:
 

select max(ID) from LOCAL_MEMBERS;

2. Create a sequence for that table, starting with the maximum number already in the ID column, and adding one. For example, if the maximum number is 50, create the following sequence:

create sequence LOCAL_MEMBERS_seq start with 51;

3. Create a "before trigger" to allow ListManager to transparently create new rows. It will then fill in the member ID, which ListManager doesn't specify when inserting rows.

create trigger localmemtrig before insert on LOCAL_MEMBERS
for each row
begin
select LOCAL_MEMBERS_seq.nextval into :new.ID from dual;
end;
/

Other steps needed to use a foreign members table may be found in Using a Foreign Members Table.

Creating a Sequence for Postgres

This example presumes you want to use a foreign members table with the name "LOCAL_MEMBERS", using the column "ID" as the Member ID column.

1. Find out the maximum number already in the id column:
 

select max(ID) from LOCAL_MEMBERS;

2. Create a sequence for that table, starting with the maximum number already in the ID column, and adding one. For example, if the maximum number is 50, create the following sequence:

create sequence LOCAL_MEMBERS_seq start 51;

3. Create a default rule to allow ListManager to transparently create new rows. It will then fill in the member ID, which ListManager doesn't specify when inserting rows.

alter table LOCAL_MEMBERS
alter column ID
set default nextval('LOCAL_MEMBERS_seq');

Other steps needed to use a foreign members table may be found in Using a Foreign Members Table.