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.Suppose you have this situation:

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|||thanks man- i'll let you know how it goes in a few.|||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