创建一个触发器来在其中运行标量值函数FOR INSERT [英] Create a trigger to run scalar-valued function in it FOR INSERT
本文介绍了创建一个触发器来在其中运行标量值函数FOR INSERT的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我创建了一个触发器,如下所示,检查UDF是否可用,如果然后DROP它们并创建新的UDF,最后在名为TableToClean的表上使用它们。
但它不是工作
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.
推荐答案
',''),'£',''),'€ ',''),'↵','')
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和@End> 0和@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(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
', ''), '£', ''), '€', ''), '↵', '') 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.
它会起作用。 如果您发现这是一个解决方案给它一个评级。 : - )
USE [DB2]
GO
/ ***** *对象:触发器[dbo]。[cleanData]脚本日期:2014年7月22日下午3:33:19 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo]。[cleanData]
ON [dbo ]。[TableToClean] FOR INSERT
AS
BEGIN
设定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和@End> 0和@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(@StyleText)), ''
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)), ''
'','''',''£'','''','''',''''','''' ','''')
结束'
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 @开始> 0和@End> 0和@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(RTRIM(@ScriptText)),'' ='','''',''\'',''''',''||'','''')
结束'
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
'', ''''), ''£'', ''''), ''€'', ''''), ''↵'', '''')
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
这篇关于创建一个触发器来在其中运行标量值函数FOR INSERT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文