Create a login table question

lj4three · #1 (**permalink**) 02-17-04, 11:43

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.

Littlefoot · #2 (**permalink**) 02-18-04, 03:10

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_name, 1, 1)

                || SUBSTR (:NEW.last_name, 1, 4)

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

lj4three · #3 (**permalink**) 02-18-04, 15:23

thanks man- i'll let you know how it goes in a few.

lj4three · #4 (**permalink**) 02-19-04, 11:56

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

Internet Services

Web Development

Digital Marketing

Consumer Tech