Create a string from inserted.id via trigger

I’m creating a trigger on insert that will create a new record in another table using the id from the newly inserted row.

I need to format the id before I insert it into the other table. This is my code so far…. having problems creating the formated @PseudoID.

CREATE TRIGGER OnInsertCreateUnallocatedPseudo 
   ON  tblTeams 
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    DECLARE @PseudoID NVARCHAR(50), @tmID NVARCHAR(10)

    SELECT @tmID = CONVERT(nvarchar(10),inserted.tmID) FROM inserted

    --NEED SOME CODE TO CREATE A PADDED OUT PseudoID e.g.
    -- if @tmID =  '7' then @PseudoID = 'PSU0007'
    -- if @tnID = '25' then @PseudoID = 'PSU0025'


    INSERT INTO [dbo].[tblUsersPseudo].....
END

Answer

You can’t assign it to a variable within the trigger since there could be multiple rows, but you could do something like this to insert into the other table.

LEFT('PSU0000',LEN('PSU0000')-LEN(CONVERT(nvarchar(10),inserted.tmID))) + CONVERT(nvarchar(10),inserted.tmID)

Advertisement

Answer