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.
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
FOR EACH ROW
INSERT INTO logins
SUBSTR (:NEW.member_name, 1, 1)
|| SUBSTR (:NEW.last_name, 1, 4)
|| TO_CHAR (:NEW.member_id),
insert into members values (1, 'john', 'smith');
select * from members;
MEMBER_ID MEMBER_NAM LAST_NAME
---------- ---------- ----------
5 john smith
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
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),