asked on

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

Mark Wills

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

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

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

mike247

ASKER

This works. I just needed to make it so that it only fires only upon insert and not upon update. Thanks!