Below is the code of a trigger, basically it calls a function in case the record that is being updated does not have other row associated by it's Id in another table. In a multiple row update this trigger fails, how can I edit it to make compatible?
declare @slugExists int =(select Id from [dbo].[UrlRecord] where EntityId = (select Id from inserted) and EntityName = 'Category') if(@slugExists is null) begin declare @slug nvarchar (200) = (select Name from inserted) EXEC @slug = dbo.GenerarSlug @str= @slug insert into [dbo].[UrlRecord] ([EntityId],[EntityName],[Slug],[IsActive],[LanguageId]) values ( (select Id from inserted) ,'Category',@slug,1,0) end
Based in comments, here is the code of the function GenerarSlug :
ALTER FUNCTION [dbo].[GenerarSlug] ( @str VARCHAR(500), @modificador VARCHAR(3) = NULL ) RETURNS VARCHAR(500) AS BEGIN DECLARE @IncorrectCharLoc SMALLINT SET @str = LOWER(@str) SET @IncorrectCharLoc = PATINDEX('%[^0123456789abcdefghijklmnopqrstuvwxyz ]%',@str) WHILE @IncorrectCharLoc > 0 BEGIN SET @str = STUFF(@str,@incorrectCharLoc,1,'') SET @IncorrectCharLoc = PATINDEX('%[^0123456789abcdefghijklmnopqrstuvwxyz ]%',@str) END SET @str = REPLACE(@str,' ','') SET @str = REPLACE(@str,' ','') SET @str = REPLACE(@str,' ','-') IF(@MODIFICADOR IS NOT NULL) SET @str = @str + @MODIFICADOR RETURN @str END
It transforms 'Especial Chárs' to 'especial-chrs' The reason I put this in a function is that it will be called from a few triggers in database so I don't repeat it each time