Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Charlotte, NC, USA
    Posts
    3

    Unanswered: Create a login table question

    Hey all- I have been reading this forum for quite some time now but I have never posted- this place is a great tool for young developers.

    Anyway, I have a specific question.

    I have a table (members)- with PK member_id- that holds member names and personal info etc.

    My goal is for each member_id in the members table, insert that member_id into my fk column in a logins table. then, when each member_id is inserted, i want to insert the first initial of the member_name and first 4 characters of the last_name, along with the member_id into the logins table in the login_name column.

    i also want to insert a unique 6 digit string into the logins table (login_password). I'm not great with script, so any help would be appreciated.

    Thanks,

    sudeep.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Suppose you have this situation:

    PHP Code:

    CREATE TABLE members
    (member_id      NUMBER(2)    CONSTRAINT pk_mem PRIMARY KEY,
     
    member_name    VARCHAR2(10),
     
    last_name      VARCHAR2(10)
    );

    CREATE TABLE logins
    (member_id      NUMBER(2)    CONSTRAINT fk_log_mem
                                 REFERENCES members 
    (member_id)
                                 
    INITIALLY DEFERRED DEFERRABLE,
     
    login_name     VARCHAR2(20),
     
    login_password VARCHAR2(6)
    );

    CREATE SEQUENCE seq_mem_login START WITH 100000;
    /* The sequence is used to generate unique login passwords in this example */

    CREATE OR REPLACE TRIGGER trg_member
       AFTER INSERT
       ON members
       
    FOR EACH ROW
    BEGIN
       INSERT INTO logins
                   
    (member_id,
                    
    login_name,
                    
    login_password
                   
    )
            
    VALUES (:NEW.member_id,
                       
    SUBSTR (:NEW.member_name11)
                    || 
    SUBSTR (:NEW.last_name14)
                    || 
    TO_CHAR (:NEW.member_id),
                    
    seq_mem_login.NEXTVAL
                   
    );
    END;
    /

    insert into members values (1'john''smith');

    select from members;
     
    MEMBER_ID MEMBER_NAM LAST_NAME
    ---------- ---------- ----------
             
    5 john       smith

    select 
    from logins;
     
    MEMBER_ID LOGIN_NAME           LOGIN_
    ---------- -------------------- ------
             
    5 jsmit5               100000 
    Last edited by Littlefoot; 02-18-04 at 04:12.

  3. #3
    Join Date
    Feb 2004
    Location
    Charlotte, NC, USA
    Posts
    3
    thanks man- i'll let you know how it goes in a few.

  4. #4
    Join Date
    Feb 2004
    Location
    Charlotte, NC, USA
    Posts
    3
    whats wrong with this code? anyone?

    it gives me the following error:

    Server: Msg 128, Level 15, State 1, Procedure trigPassword_CreateMember, Line 9
    The name 'member_id' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.


    --Create the trigger
    CREATE TRIGGER trigPassword_CreateMember ON Accounts
    AFTER Insert
    AS
    BEGIN
    BEGIN TRAN
    INSERT INTO Logins(member_id, login_name, login_password)
    VALUES (inserted.member_id, SUBSTRING(inserted.first_name,1,1) + SUBSTRING(last_name,1,1) + CONVERT(char(5),inserted.member_id),
    inserted.account_id)
    COMMIT TRAN
    END
    GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •