Je suis un novice en SQL.Je crée un projet factice.Dans mon projet, j'ai une page de registre et je veux créer un UserName (Prénom + Nom + UserID) .Mais le problème est, je ne peux pas attraper la valeur UserID.I ont utilisé à la fois After Trigger et à la place de Trigeer. Voici mon script –
Table –
CREATE TABLE UserInfo ( UserID INT IDENTITY(1,1), FirstName NVARCHAR(500), LastName NVARCHAR(500), [Password] NVARCHAR(200), EmailID NVARCHAR(200), [Address] NVARCHAR(500), CountryID INT, StateID INT, UserName NVARCHAR(500) )
Proc de l'insertion des données –
CREATE PROC Create_User @FirstName NVARCHAR(500), @LastName NVARCHAR(500), @Password NVARCHAR(200), @EmailID NVARCHAR(200), @Address NVARCHAR(500), @CountryID INT, @StateID INT, @UserID INT OUTPUT AS BEGIN INSERT INTO UserInfo (FirstName,LastName,[Password],EmailID,[Address],CountryID,StateID) VALUES (@FirstName,@LastName,@Password,@EmailID,@Address,@CountryID,@StateID) SET @UserID = SCOPE_IDENTITY(); END
Après le triggersment –
CREATE TRIGGER Create_UserName ON UserInfo AFTER INSERT AS BEGIN DECLARE @_UName VARCHAR(200); SET @_UName = (SELECT FirstName+LastName+Cast(UserID AS NVARCHAR(100)) from INSERTED ) INSERT INTO UserInfo (UserName) VALUES (@_UName) END GO
Sortie – Inséré deux lignes
Au lieu de Trigger –
CREATE TRIGGER Create_UserName ON UserInfo INSTEAD OF INSERT AS BEGIN DECLARE @UName NVARCHAR(200); DECLARE @FName NVARCHAR(200); DECLARE @LName NVARCHAR(200); DECLARE @UPassword NVARCHAR(200); DECLARE @UEmailID NVARCHAR(200); DECLARE @UAddress NVARCHAR(200); DECLARE @UCountryID INT; DECLARE @UStateID INT; SET @UName = (SELECT FirstName+LastName+Cast(UserID AS NVARCHAR(100)) from INSERTED ) SET @FName = (SELECT FirstName from INSERTED ) SET @LName = (SELECT LastName from INSERTED ) SET @UPassword = (SELECT [Password] from INSERTED ) SET @UEmailID = (SELECT EmailID from INSERTED ) SET @UAddress = (SELECT [Address] from INSERTED ) SET @UCountryID = (SELECT CountryID from INSERTED ) SET @UStateID = (SELECT StateID from INSERTED ) INSERT INTO UserInfo (FirstName,LastName,[Password],EmailID,[Address],CountryID,StateID,UserName) VALUES (@FName,@LName,@UPassword,@UEmailID,@UAddress,@UCountryID,@UStateID,@UName) END GO
Sortie – cela fonctionne bien, mais l'identité est 0.
S'il te plaît, dis-moi comment je peux faire ça?
Merci d'avance.
tu as des problèmes
Quant à pourquoi:
Le proc stocké n'a pas d'INSERT pour intercepter la valeur IDENTITY: la scope de l'INSERT est en réalité la place de sortinggger. Si vous passez à @@ IDENTITY (mauvaise pratique), vous obtiendrez la valeur IDENTITY du triggersur AFTER.
Que faire:
Déposez les deux triggersurs: ils n'ajoutent aucune valeur
Ajoutez une colonne calculée à la table si le nom d'user ne peut pas être modifié
par exemple
ALTER TABLE UserInfo ADD UserName AS FirstName+LastName+Cast(UserID AS NVARCHAR(100)
par exemple
CREATE PROC Create_User ... AS SET NOCOUNT, XACT_ABORT ON BEGIN TRY BEGIN TRAN INSERT INTO UserInfo (FirstName,LastName,[Password],EmailID,[Address],CountryID,StateID) VALUES (@FirstName,@LastName,@Password,@EmailID,@Address,@CountryID,@StateID) SET @UserID = SCOPE_IDENTITY(); UPDATE UserInfo SET UserName = @FirstName+@LastName+Cast@UserID AS NVARCHAR(100) WHERE UserID = @UserID COMMIT TRAN END TRY BEGIN CATCH IF XACT_STATE() <> 0 ROLLBACK TRAN END CATCH GO
Essayez ce triggersur:
CREATE TRIGGER Create_UserName ON UserInfo AFTER INSERT AS BEGIN --DECLARE @_UName VARCHAR(500), @user_id INT --SELECT @_UName = FirstName + LastName + Cast(UserID AS NVARCHAR(100)), @user_id = UserId FROM INSERTED UPDATE UserInfo SET UserName = i.FirstName + i.LastName + Cast(i.UserID AS NVARCHAR(100)) FROM UserInfo u INNER JOIN Inserted I ON (i.UserId = u.UserId) --WHERE UserId = @user_id END
Si vous avez SQL Server 2005+, vous pouvez utiliser la clause output de l'insertstatement.
CREATE PROC Create_User @FirstName NVARCHAR(500), @LastName NVARCHAR(500), @Password NVARCHAR(200), @EmailID NVARCHAR(200), @Address NVARCHAR(500), @CountryID INT, @StateID INT, @UserID INT OUTPUT AS BEGIN DECLARE @OV Table (id int); INSERT INTO UserInfo (FirstName,LastName,[Password],EmailID,[Address],CountryID,StateID) inserted.id into @OV VALUES (@FirstName,@LastName,@Password,@EmailID,@Address,@CountryID,@StateID) SELECT @UserID = id FROM @OV; update UserInfo set UserName = FirstName + LastName + Cast(UserID AS NVARCHAR) where UserID = @userid END
EDIT: Mot key manquant ajouté, mise à jour de la colonne UserName