当前位置: 动力学知识库 > 问答 > 编程问答 >

Data-cleansing trigger help for MySQL, please

问题描述:

I am extremely new to the MySQL environment and databases in general. I am currently working on a project for work and am having a great deal of difficulty trying create a MySQL trigger. I'm sure there are a number of issues with the below script as it was pieced together from code I found here that was relevant to what I was trying to do, and this is the first time I've ever done anything like this.

The purpose of the script is supposed to be to take an input that is coming from a another program and remove all the spaces and special characters before it inserts or updates into the DB. Below is what I've got so far based on the research I've done so far. Please any help would be greatly appreciated.

DELIMITER $$

CREATE TRIGGER Insert_Name_cleaner BEFORE INSERT ON @DWDatabase

FOR EACH ROW

BEGIN

;with t1 as

(

select first_name, cleaned_first_name, ID from @DWDatabase

union all

select cast(replace(cleaned_first_name, substring(cleaned_first_name, PatIndex('% [^a-z]%', cleaned_first_name), 1), '') as varchar(60)), id

from t1

where PatIndex('%[^a-z]%', cleaned_first_name) > 0

)

select t2.*, t1.cleaned_first_name from t1

join @DWDatabase t2

on t1.id = t2.id

where PatIndex('%[^a-z]%', cleaned_first_name) = 0

option (maxrecursion 0)

END$$

CREATE TRIGGER update_clean_name BEFORE UPDATE ON @DWDatabase

FOR EACH ROW

BEGIN

;with t1 as

(

select first_name, cleaned_first_name, ID from @DWDatabase

union all

select cast(replace(cleaned_first_name, substring(cleaned_first_name, PatIndex('%[^a-z]%', cleaned_first_name), 1), '') as varchar(60)), id

from t1

where PatIndex('%[^a-z]%', cleaned_first_name) > 0

)

select t2.*, t1.cleaned_first_name from t1

join @DWDatabase t2

on t1.id = t2.id

where PatIndex('%[^a-z]%', cleaned_first_name) = 0

option (maxrecursion 0)

END$$

DELIMITER ;

Again any help that can be provided will be greatly appreciated, I've hit a very large wall in my research and can't seem to progress any further than this.

分享给朋友:
您可能感兴趣的文章:
随机阅读: