April 9, 2012 at 11:56 am
Hello all,
I have a table where I need to format a string before it inserts or updates, I think I need a trigger but I don't know how to go about getting what I need. Hope someone here can help.
The string I need to format is like. Uniroyal+Bridge, what I want is spaces before and after the + sign.
So the above would be Uniroyal + Bridge, and there can be multiple + signs.
Thinking also regex could help.
Thanks,
Keith.
April 9, 2012 at 12:00 pm
Are those values from other columns? Values from input parameters for a stored procedure? Values from a direct insert by the application? Something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2012 at 12:05 pm
They are direct insert from a classic asp application.
It's being dynamically created using syntax like element and looping.
It's used mulitiple times to insert different values in different tables, so I can't get that specific value from the application and format it there.
I think it has to be done on the DB side, by a trigger.
April 9, 2012 at 12:10 pm
When would the trigger fire? After all the inserts/updates are done?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2012 at 12:12 pm
Sorry about that, I would like it to fire before the insert/update.
so the data is in the correct format.
so instead of name+name I will insert this as name + name. *the space before and after the + sign.
April 9, 2012 at 12:22 pm
I'm not sure that you really understand what a trigger is. A trigger fires when an event occurs. If there is no event, there is no firing; i.e. before an insert is the lack of an event. You need to change the SQL to format the data before it inserts it. Looking for the presence of the '+' by using charindex() or patindex(), depending on your specific scenarios. I would also look into calling a stored proc instead of creating the SQL dynamically with the application. That will cause you problems in maintenance, security, and data integrity.
Jared
CE - Microsoft
April 9, 2012 at 12:26 pm
kstephens 90242 (4/9/2012)
Sorry about that, I would like it to fire before the insert/update.so the data is in the correct format.
so instead of name+name I will insert this as name + name. *the space before and after the + sign.
Hi,
You can use INSTEAD OF INSERT in your trigger.
Take in mind that when you make INSERT, the copies are also in the INSERTED table, and when you make UPDATE operation the old version of the data (for the key you make update) is in the DELETED table, and the INSERTED table holds the rows after the update.
Igor Micev,My blog: www.igormicev.com
April 9, 2012 at 12:28 pm
You can sort of do that if you're updating one table, or always updating the same table last.
A trigger fires either when the data is inserted/updated, or instead of the data being inserted/updated. (Deleted can also have triggers, but that doesn't apply here.)
If, for example, all the data is going into "MyTable", and you want to fix a "Name" column, it might look like this:
create trigger NameFix on dbo.MyTable
after update, insert
as
set nocount on;
update MT
set Name = replace(MT.Name, '+', ' + ')
from dbo.MyTable as MT
inner join inserted
on MT.ID = inserted.ID;
That trigger uses the "inserted" table, which has the "after" values for an update and the new values for an insert, to limit it to only fixing new/updated rows.
I can't get more specific than that without definitions of the table(s) that you're working with, and some sample insert statements to put data into them. Is that something you can provide?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2012 at 12:32 pm
Thanks GSquared
I was heading down that way with what I found at.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply