Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 

EDIT

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

share|improve this question
    
What does the procedure dbo.GenerarSlug, I suspect the most efficient solution is going to be to expand the logic contained within this out to a set based approach in the trigger. Cursors in triggers will kill your database performance. –  GarethD Jun 6 at 10:13
    
It creates a url safe string from a normal string. Please explain your comment "set based approach" a little. I don´t like cursors too much but if there is not another solution I would use it –  Msam85 Jun 6 at 11:27
    
Can you post the code for your procedure? Without this it is impossible to suggest an improvement. I expect even with a single inserted value this code is not doing what you think. EXEC @slug = dbo.GenerarSlug @str= @slug will set @slug to the return value if the procedure, which has to be an integer (usually used for providing the status of the procedure execution). –  GarethD Jun 6 at 11:53
1  
@GarethD It is a function, not a procedure. The EXEC looks weird, but it is correct (try it!). Msam85, you really do need to post the function code to get a good answer here. "set based" = processing the whole set at once, rather than row-by-row in a cursor or loop. –  Paul White Jun 6 at 15:09
    
@PaulWhite Every day is a school day! That's why I love stackexchange. In all my years of SQL Server experience I have never seen a function called this way. –  GarethD Jun 6 at 15:29

1 Answer 1

up vote 2 down vote accepted

If dbo.Generarslug is a scalar function, then you could use the following:

INSERT[dbo].[UrlRecord] ([EntityId],[EntityName],[Slug],[IsActive],[LanguageId])
SELECT  i.id,
        'Category', 
        dbo.GenerarSlug(i.Name),
        1,
        0
FROM    inserted AS i
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    [dbo].[UrlRecord] AS r
            WHERE   r.EntityID = i.ID
            AND     r.EntityName = 'Category'
        );

There is still potentially scope for improvement depending on what dbo.GenerarSlug actually does.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.