Create a trigger to run scalar-valued function in it FOR INSERT

Question

1.00/5 (1 vote)

See more:

I created a trigger as below to check whether the UDF are available or not if are then DROP them and create new UDF, then at last Use them on a table named TableToClean.
But it's not working

USE [DB2]
GO
/****** Object:  Trigger [dbo].[cleanData]    Script Date: 7/22/2014 3:33:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[cleanData]
ON [dbo].[TableToClean] FOR INSERT
AS
BEGIN
            SET NOCOUNT ON
            IF OBJECT_ID('[dbo].[cleanStyleData]') IS NOT NULL
             DROP FUNCTION cleanStyleData
            GO

        CREATE FUNCTION [dbo].[cleanStyleData] ( @StyleText VARCHAR(MAX) )
        RETURNS VARCHAR(MAX)
        AS BEGIN
            DECLARE @Start INT
            DECLARE @End INT
            DECLARE @Length INT
            SET @Start = CHARINDEX('<style>', @StyleText)
            SET @End = CHARINDEX('</style>', @StyleText, @Start)
            SET @Length = ( @End - @Start ) + 1
            WHILE @Start > 0 AND @End > 0 AND @Length > 0
                BEGIN
                    SET @StyleText = STUFF(@StyleText, @Start, @Length, '')
                    SET @Start = CHARINDEX('<style>', @StyleText)
                    SET @End = CHARINDEX('</style>', @StyleText, @Start)
                    SET @Length = ( @End - @Start ) + 1
                END
            RETURN REPLACE(REPLACE( REPLACE(REPLACE(LTRIM(RTRIM(@StyleText)), '$', ''), '£', ''), '€', ''), '↵', '')
        END

        IF OBJECT_ID('[dbo].[cleanScriptData]') IS NOT NULL
         DROP FUNCTION cleanScriptData
        GO

        CREATE FUNCTION [dbo].[cleanScriptData] ( @ScriptText VARCHAR(MAX) )
        RETURNS VARCHAR(MAX)
        AS BEGIN
            DECLARE @Start INT
            DECLARE @End INT
            DECLARE @Length INT
            SET @Start = CHARINDEX('<script>', @ScriptText)
            SET @End = CHARINDEX('</script>', @ScriptText, @Start)
            SET @Length = ( @End - @Start ) + 1
            WHILE @Start > 0 AND @End > 0 AND @Length > 0
                BEGIN
                    SET @ScriptText = STUFF(@ScriptText, @Start, @Length, '')
                    SET @Start = CHARINDEX('<script>', @ScriptText)
                    SET @End = CHARINDEX('</script>', @ScriptText, @Start)
                    SET @Length = ( @End - @Start ) + 1
                END
            RETURN REPLACE(REPLACE( REPLACE(LTRIM(RTRIM(@ScriptText)), '=', ''), '\', ''), '||', '')
        END

        UPDATE [DB2].[dbo].[TableToClean]
        SET [DA_ArticleDetails] = [dbo].[cleanStyleData] ([DA_ArticleDetails])

        UPDATE [DB2].[dbo].[TableToClean]
        SET [DA_ArticleDetails] = [dbo].[cleanScriptData] ([DA_ArticleDetails])

END

I tried to execute it but giving errors.

Posted 22-Jul-14 1:17am

GoneBump

Add a Solution

Comments

Corporal Agarn 22-Jul-14 9:58am

Ever think of creating a function that works in the first place? You should not have to re-build it every time the data changes. Or possible have a few functions to handle different data.

GoneBump 22-Jul-14 10:16am

Actually the requirement is to break the cleaning in steps. Creating a single UDF will make a overload as the loop will have to do it at a time. Breaking it provides a clear picture for improvisation.

Mayank Vashishtha 23-Jul-14 5:13am

Your approach is wrong GoneBump. You cannot create a function inside a trigger which is also a kind of stored procedure in sql server. It is never going to get compiled buddy. I agree with Corporal Agarn of having a function already created. Moreover you can try using dynamic sql query instead.

GoneBump 23-Jul-14 5:50am

Thanks!!!
But how to call thoes UDF inside this trigger?
Will this work if the UDF are created before the trigger run?

UPDATE [DB2].[dbo].[TableToClean]
SET [DA_ArticleDetails] = [dbo].[cleanStyleData] ([DA_ArticleDetails])

Mayank Vashishtha 23-Jul-14 6:08am

Have you checked my solution?

GoneBump 23-Jul-14 6:18am

Yes. It works when I used it like this:

DECLARE @Command NVARCHAR(MAX)
SET @command = '
UPDATE [NetCrawlerDemo].[dbo].[DA_TrackerInfo_SGA2]
SET [DA_ArticleDetails] = [dbo].[cleanStyleData] ([DA_ArticleDetails])'
EXEC(@Command)

SET @command = '
UPDATE [NetCrawlerDemo].[dbo].[DA_TrackerInfo_SGA2]
SET [DA_ArticleDetails] = [dbo].[cleanScriptData] ([DA_ArticleDetails])'
EXEC(@Command)

I have created the UDF prior to use them as suggested.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mayank Vashishtha · Accepted Answer · 2014-07-22T23:20:00

It will work. If you find this as a solution Give it a rating. :-)

USE [DB2]
GO
/****** Object: Trigger [dbo].[cleanData] Script Date: 7/22/2014 3:33:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[cleanData]
ON [dbo].[TableToClean] FOR INSERT
AS
BEGIN

SET NOCOUNT ON
IF OBJECT_ID('[dbo].[cleanStyleData]') IS NOT NULL
DROP FUNCTION cleanStyleData

DECLARE @Query1 NVARCHAR(MAX), @Query2 NVARCHAR(MAX)

SET @Query1 = 'CREATE FUNCTION [dbo].[cleanStyleData] ( @StyleText VARCHAR(MAX) )
RETURNS VARCHAR(MAX)
AS BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
SET @Start = CHARINDEX(''<style>'', @StyleText)
SET @End = CHARINDEX(''</style>'', @StyleText, @Start)
SET @Length = ( @End - @Start ) + 1
WHILE @Start > 0 AND @End > 0 AND @Length > 0
BEGIN
SET @StyleText = STUFF(@StyleText, @Start, @Length, '''')
SET @Start = CHARINDEX(''<style>'', @StyleText)
SET @End = CHARINDEX(''</style>'', @StyleText, @Start)
SET @Length = ( @End - @Start ) + 1
END
RETURN REPLACE(REPLACE( REPLACE(REPLACE(LTRIM(RTRIM(@StyleText)), ''$'', ''''), ''£'', ''''), ''€'', ''''), ''↵'', '''')
END'

EXECUTE sp_executesql @Query1

IF OBJECT_ID('[dbo].[cleanScriptData]') IS NOT NULL
DROP FUNCTION cleanScriptData

SET @Query2 = 'CREATE FUNCTION [dbo].[cleanScriptData] ( @ScriptText VARCHAR(MAX) )
RETURNS VARCHAR(MAX)
AS BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
SET @Start = CHARINDEX(''<script>'', @ScriptText)
SET @End = CHARINDEX(''</script>'', @ScriptText, @Start)
SET @Length = ( @End - @Start ) + 1
WHILE @Start > 0 AND @End > 0 AND @Length > 0
BEGIN
SET @ScriptText = STUFF(@ScriptText, @Start, @Length, '''')
SET @Start = CHARINDEX(''<script>'', @ScriptText)
SET @End = CHARINDEX(''</script>'', @ScriptText, @Start)
SET @Length = ( @End - @Start ) + 1
END
RETURN REPLACE(REPLACE( REPLACE(LTRIM(RTRIM(@ScriptText)), ''='', ''''), ''\'', ''''), ''||'', '''')
END'

EXECUTE sp_executesql @Query2

UPDATE [DB2].[dbo].[TableToClean]
SET [DA_ArticleDetails] = [dbo].[cleanStyleData] ([DA_ArticleDetails])

UPDATE [DB2].[dbo].[TableToClean]
SET [DA_ArticleDetails] = [dbo].[cleanScriptData] ([DA_ArticleDetails])
END