November 2, 2014 at 11:14 am
Here is my problem. I want to create a trigger that will automatically take out the tags from HTML code from a table cell like
<UL><li>some text here</li> <li>some text here</li> <li>some text here</li> </UL>
And parse it in to separate rows in the same table like
ROW 1: some text here
ROW 2: some text here
ROW 3: some text here
These new rows will also have the primary key and a bookkey value and sortorder value attached as well...
The bookkey will be the same for all 3 rows taken from the bookkey value in the original row, but the sort order will be the number of parsed rows in sequential order.
I have an idea to use a trigger to convert these HTML tags into new rows and delete the original, but I cant figure out how to do it.
Any ideas?
November 2, 2014 at 12:44 pm
Hi and welcome to the forum, here is a quick and straight forward sample, should be enough to get you passed this hurdle.
😎
Note: Suggest that you revise the design rather than using a trigger for this if possible.
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID('dbo.UPDATE_TRIGGER_TEST') IS NOT NULL DROP TABLE dbo.UPDATE_TRIGGER_TEST;
CREATE TABLE dbo.UPDATE_TRIGGER_TEST
(
UTT_ID INT NOT NULL
,UTT_RID INT NOT NULL
,UTT_VAL NVARCHAR(MAX) NOT NULL
,CONSTRAINT PK_DBO_UPDATE_TRIGGER_TEST_UTT_ID PRIMARY KEY CLUSTERED (UTT_ID,UTT_RID)
)
GO
CREATE TRIGGER TRG_DBO_UPDATE_TRIGGER_TEST_SPLIT_ROWS
ON dbo.UPDATE_TRIGGER_TEST
AFTER INSERT
AS
DECLARE @SPLITS TABLE
(
UTT_ID INT NOT NULL
,UTT_RID INT NOT NULL
,UTT_VAL NVARCHAR(MAX) NOT NULL
);
INSERT INTO @SPLITS(UTT_ID,UTT_RID,UTT_VAL)
SELECT
ML.UTT_ID
,ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) AS BD_RID
,BASE.DATA.value('text()[1]','NVARCHAR(100)')
FROM (select
I.UTT_ID
,ROW_NUMBER() OVER
(
PARTITION BY I.UTT_ID
ORDER BY (SELECT NULL)
) AS BD_RID
,CONVERT(XML,I.UTT_VAL,1) AS XHTML FROM inserted I) AS ML
OUTER APPLY ML.XHTML.nodes('UL/*') AS BASE(DATA);
DELETE IX
FROM inserted I
INNER JOIN dbo.UPDATE_TRIGGER_TEST IX
ON I.UTT_ID = IX.UTT_ID;
INSERT INTO dbo.UPDATE_TRIGGER_TEST(UTT_ID,UTT_RID,UTT_VAL)
SELECT S.UTT_ID,S.UTT_RID,S.UTT_VAL
FROM @SPLITS S;
GO
INSERT INTO dbo.UPDATE_TRIGGER_TEST(UTT_ID,UTT_RID,UTT_VAL)
VALUES (1,1,N'<UL><li>some text here1</li> <li>some text here2</li> <li>some text here3</li> </UL>');
SELECT
T.UTT_ID
,T.UTT_RID
,T.UTT_VAL
FROM dbo.UPDATE_TRIGGER_TEST T;
INSERT INTO dbo.UPDATE_TRIGGER_TEST(UTT_ID,UTT_RID,UTT_VAL)
VALUES (2,1,N'<UL><li>some text here4</li> <li>some text here5</li> <li>some text here6</li> </UL>');
SELECT
T.UTT_ID
,T.UTT_RID
,T.UTT_VAL
FROM dbo.UPDATE_TRIGGER_TEST T;
Result 1
UTT_ID UTT_RID UTT_VAL
----------- ----------- -----------------
1 1 some text here1
1 2 some text here2
1 3 some text here3
Result 2
UTT_ID UTT_RID UTT_VAL
----------- ----------- -----------------
1 1 some text here1
1 2 some text here2
1 3 some text here3
2 1 some text here4
2 2 some text here5
2 3 some text here6
Edit: typo
November 2, 2014 at 4:44 pm
Thanks for the help, however because I am still a noob, I dont quite get it. So i tried to fit it to my table.
I get this
Msg 311, Level 16, State 1, Procedure TRG_DBO_UPDATE_TRIGGER_TEST_SPLIT_ROWS, Line 51
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
I original table has a bunch of columns, so i added the ones that could not be null in here..
I am not sure what else to do, but it looks like I am not getting anywhere with this trigger.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
CREATE TRIGGER TRG_DBO_UPDATE_TRIGGER_TEST_SPLIT_ROWS
ON dbo.bookcomments
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SPLITS TABLE
(
bookkey INT NOT NULL
,printingkey INT NOT NULL
,commenttypecode INT NOT NULL
,commenttypesubcode INT NOT NULL
,sortorder INT NULL
,commenthtml TEXT NULL
)
INSERT INTO @SPLITS(bookkey,printingkey,commenttypecode,commenttypesubcode,sortorder,commenthtml)
SELECT
ML.bookkey
,ML.printingkey
,ML.commenttypecode
,ML.commenttypesubcode
,ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) AS BD_sortorder
,BASE.DATA.value('text()[1]','NVARCHAR(100)')
FROM (select
I.bookkey
,I.printingkey
,I.commenttypecode
,I.commenttypesubcode
,ROW_NUMBER() OVER
(
PARTITION BY I.bookkey
ORDER BY (SELECT NULL)
) AS BD_sortorder
,CONVERT(XML,I.commenthtml,1) AS XHTML FROM inserted I) AS ML
OUTER APPLY ML.XHTML.nodes('UL/*') AS BASE(DATA)
DELETE IX
FROM inserted I
INNER JOIN dbo.bookcomments IX
ON I.bookkey = IX.bookkey
INSERT INTO dbo.bookcomments(bookkey,printingkey,commenttypecode,commenttypesubcode,sortorder,commenthtml)
SELECT S.bookkey,S.printingkey,S.commenttypecode,S.commenttypesubcode,S.sortorder,S.commenthtml
FROM @SPLITS S
END
INSERT INTO dbo.bookcomments(bookkey,printingkey,commenttypecode,commenttypesubcode, sortorder,commenthtml)
VALUES (1,1,1,1,1,N'<UL><li>some text here1</li> <li>some text here2</li> <li>some text here3</li> </UL>');
SELECT
T.bookkey
,T.printingkey
,T.sortorder
,T.commenthtml
FROM dbo.bookcomments T
November 2, 2014 at 9:36 pm
Quick question, can you change the data type for the commenthtml column from TEXT to NVARCHAR(MAX)?
😎
November 2, 2014 at 9:40 pm
I suppose I could. I am open to ideas.
November 2, 2014 at 10:25 pm
If you do then the trigger should work. Can you post a DDL (create table) script for the table?
😎
November 3, 2014 at 8:33 am
I tried but it would need to be dropped and recreated to change the variable type. Shoot.
I attached a screenshot of the table.
November 3, 2014 at 12:11 pm
dackenshire (11/3/2014)
I tried but it would need to be dropped and recreated to change the variable type. Shoot.I attached a screenshot of the table.
You should still change the data type from text to varchar(max)/nvarchar(max), the text data type is depreciated and will be dropped out of future versions of SQL Server. The Management Studion (SSMS) will give you a warning but you can even turn off the warning, nothing really to worry about unless it's a very large table. SSMS will generate a proper script to do this, copying all the data into a new table, drop the old one and rename the new to the same name as the old one.
😎
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply