trigger on insert to update column if contain top 2012 and replace by top 2013

i have the following table

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [ActionId]
      ,[Text]
      ,[UserId]
      ,[type]
      ,[ActionTypeId]
  FROM [HITV_Filtration].[TvUser].[Sendsms]
  where Text like '%top5 2012%'
  order by actionid desc


i want to replace each new record inserted in [HITV_Filtration].[TvUser].[Sendsms]
contain '%top5 2012%' and replace only the top5 2012 by top5 2013

the Text is like : vote to 23232 for top5 2012  XXXX
it should be replacd by vote to 23232 for top5 2012 XXX

??? i should care on update the primarykey is actionid

please can anyone help me on write this function
LVL 1
AFIF JABADOAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
the Text is like : vote to 23232 for top5 2012  XXXX
it should be replacd by vote to 23232 for top5 2012 XXX
I suspect you meant:
the Text is like : vote to 23232 for top5 2012  XXXX
it should be replacd by vote to 23232 for top5 2013 XXX

If that is the case then try it like this:
UPDATE  T
    SET     [Text] = REPLACE([Text], 'TOP5 2012', 'TOP5 2013')
    FROM    INSERTED I
            INNER JOIN dbo.[TvUser].[Sendsms] T ON T.ActionId = I.ActionId
    WHERE   [Text] LIKE '%TOP5 2012%'

Open in new window

0
 
AFIF JABADOAuthor Commented:
CREATE TABLE [TvUser].[Sendsms](
      [ActionId] [bigint] NOT NULL,
      [Text] [varchar](4000) NULL,
      [UserId] [int] NULL,
      [type] [tinyint] NULL,
      [ActionTypeId]  AS (CONVERT([tinyint],(7),(0))),
 CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED
(
      [ActionId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TRIGGER dbo.tri_Test_Insert_Trigger
ON [TvUser].[Sendsms] FOR INSERT
AS
BEGIN
    UPDATE T
    SET Text = // replace function ??
    FROM
        INSERTED I
    INNER JOIN dbo.[TvUser].[Sendsms]  T ON
        T.ActionId = I.ActionId

WHERE .. TEXT LIKE ;%TOP5 2012%'
END
GO


please help :S
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.