Trigger to Remove Special Character (If Exists)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Trigger to Remove Special Character (If Exists)

Author

Topic

whitelockben
Starting Member

1 Post

Posted - 2014-06-10 : 05:46:10

Hi all,

I am looking for some assistance on creating a trigger that will remove a special character ('*') from a field on insert or update, if it exists in that field of the record being inserted or updated.

Can anyone point me in the right direction please?

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-06-10 : 08:30:06

Look at instead of trigger. I think there might be a better way but I cannot remember right now.

djj

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-06-11 : 19:07:40

[code]create trigger tr_iu_MyTable on dbo.MyTable
for insert, update
as
update mt
set MyColumn = Replace(mt.MyColumn, '*', '')
from dbo.MyTable mt
inner join
inserted i
on i.MyKey = mt.MyKey[/code]???

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-06-24 : 17:40:31

[code]

CREATE TRIGGER table_name_TR_INS_UPD
ON dbo.table_name
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
UPDATE tn
SET data_column = REPLACE(data_column, '*', '')
FROM dbo.table_name tn
INNER JOIN inserted i ON
i.key_col = tn.key_col
--AND i.key_col2 = tn.key_col2
WHERE
i.data_column LIKE '*'
GO

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources