• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 45
  • Last Modified:

trim prefix

I need to change the following trigger so that it removes any prefix not just "CIS-".  So everything left of the first dash including the dash needs to be removed.  We want to keep the trimming of the trailing = sign as well.  Can someone assist me in how to modify this trigger to do this?  Thanks.

ALTER TRIGGER [dbo].[Dvx_TrimCIS-VNDITNUM]
ON [dbo].[IV00103]
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON

UPDATE IV00103
SET
    VNDITNUM = REPLACE(SUBSTRING(IV00103.VNDITNUM,
        CASE WHEN IV00103.VNDITNUM LIKE 'CIS-%' THEN 5 ELSE 1 END,
        LEN(IV00103.VNDITNUM) -
        CASE WHEN IV00103.VNDITNUM LIKE 'CIS-%' THEN 4 ELSE 0 END),
        '=', '')
FROM IV00103
INNER JOIN Inserted I ON
      IV00103.ITEMNMBR = I.ITEMNMBR and
      IV00103.VENDORID = I.VENDORID and
      IV00103.ITMVNDTY = I.ITMVNDTY
WHERE
      IV00103.VNDITNUM LIKE 'CIS-%' OR
      RIGHT(IV00103.VNDITNUM, 1) = '='


GO
0
mike247
Asked:
mike247
1 Solution
 
Mark WillsTopic AdvisorCommented:
Not exactly sure what you want to do, but you can do
ALTER TRIGGER Dvx_TrimCIS-VNDITNUM on YourTableName
AFTER INSERT,UPDATE   
AS   
-- etc

Open in new window

But if it is in the name of the trigger, then drop the trigger first and redefine it.

Have a read of : https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-trigger-transact-sql
0
 
Scott PletcherSenior DBACommented:
UPDATE IV00103
SET
    VNDITNUM = REPLACE(SUBSTRING(IV00103.VNDITNUM, CHARINDEX('-', IV00103.VNDITNUM) + 1, 8000), '=', '')
FROM IV00103
INNER JOIN Inserted I ON
      IV00103.ITEMNMBR = I.ITEMNMBR and
      IV00103.VENDORID = I.VENDORID and
      IV00103.ITMVNDTY = I.ITMVNDTY
WHERE
      IV00103.VNDITNUM LIKE '%-%' OR
      IV00103.VNDITNUM LIKE '%='
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Something like:
UPDATE IV00103 
SET VNDITNUM = REPLACE(SUBSTRING(IV00103.VNDITNUM, CHARINDEX('-', IV00103.VNDITNUM)+1, LEN(IV00103.VNDITNUM) - CHARINDEX('-', IV00103.VNDITNUM)),'=', '')
FROM IV00103
INNER JOIN Inserted I ON
      IV00103.ITEMNMBR = I.ITEMNMBR and 
      IV00103.VENDORID = I.VENDORID and 
      IV00103.ITMVNDTY = I.ITMVNDTY
WHERE
      IV00103.VNDITNUM LIKE 'CIS-%' OR
      RIGHT(IV00103.VNDITNUM, 1) = '='

Open in new window

0
 
mike247Author Commented:
This works.  I just needed to make it so that it only fires only upon insert and not upon update.  Thanks!
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now