January 18, 2007 at 2:18 pm
Dear Experts;
I'm trying to create a trigger that replaces a login ID field (LOGIN_EMPL_ID) with the correct data to make it LDAP compliant with our A/D. We create logins in A/D using last_name & if a last name already exists, we use 1st initial + last_name.
With the trigger below, regardless if there are duplicate last names or not, the first initial is always getting added to the last name when populating the LOGIN_EMPL_ID field. I'm sure the problem lies in the use of the “Select Top 1” in the statement, but I fail to see how to get away from using this so I don't get a multiple rows error in the sub-query within the IF statements.
Any ideas?
Thanks for your help
CREATE TRIGGER tr_Updt_Login_Empl_ID
ON [TC_0002].[EMPL]
/* Update Login_Empl_ID to users last name or first initial + last name (when last name already exists).*/
FOR INSERT AS
DECLARE @empl_id VARCHAR(20)
DECLARE @Last_Name VARCHAR(30)
DECLARE @FI VARCHAR (1)
DECLARE @Login_Empl_ID VARCHAR(30)
SELECT @empl_id = (SELECT empl_id FROM Inserted)
SELECT @Last_Name = (SELECT Last_Name FROM Inserted)
SELECT @FI = (SELECT substring(First_Name,1,1) FROM Inserted)
/* Populate the Login_Empl_ID variable*/
IF (SELECT TOP 1 Last_Name
FROM TC_0002.EMPL
WHERE Last_Name = @Last_Name) = @Last_Name
BEGIN
SET @Login_Empl_ID = (SELECT @FI+@Last_Name
FROM Inserted)
END
ELSE
BEGIN
SET @Login_Empl_ID = (SELECT @Last_Name
FROM Inserted)
END
/* Update Login_Empl_ID field in EMPL table*/
IF (SELECT top 1 Last_Name
FROM TC_0002.EMPL
WHERE Last_Name = @Last_Name) = @Last_Name
BEGIN
UPDATE tc_0002.empl
SET Login_Empl_ID = @FI+@Last_Name
WHERE empl_id = @empl_id
END
ELSE
BEGIN
UPDATE tc_0002.empl
SET Login_Empl_ID = @Last_Name
WHERE empl_id = @empl_id
END
January 18, 2007 at 2:30 pm
You're trigger is written to only support 1 record at a time inserts to the table. Can you ever have scenarios where the number of records inserted in a batch (and therefore the number of records in 'inserted') is more than 1 ?
Also, what should happen if there's a very common name and John Smith and Jason Smith are already in the table as 'smith' and 'jsmith' when Janet Smith joins the company ?
January 18, 2007 at 3:15 pm
Here’s what works!
IF (SELECT Count(*)
FROM TC_0002.EMPL
WHERE Last_Name = @Last_Name) > 1
Thanks for the help.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply