Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an MSSQL Server 2008 Database.
There are some tables in my Database like below :
table1, table2, table3, ....
each table has a NVARCHAR(50) field (Name)
Now, I wanna change the strings that each sql query inserts in this field
e.g :
We have a query that wanna insert "Sample Text" in Name field.
I wanna change it to "Sample Name" with a SQL Trigger

Is it Possible with sql triggers ? if so, How? I'm naive in T-SQL
Is there a better way to do it with SQL Server ?

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

if you want to use triggers thats your call, i am just suggesting a different approach

you can use the Default Value or Binding feature of a table

e.g in your table1, you can make the default value of Name column = 'Sample Name' by below script

ALTER TABLE table1 ADD CONSTRAINT
    DF_Table1_Name DEFAULT N'Sample Name' FOR Name
GO

Look at image below Table Default value of Column


This code below adds a trigger to your table that works after insert, it updates the name added and replaces the 'x' with '*'

CREATE TRIGGER ReplacingTrigger 
ON  table1  
AFTER INSERT 
AS  
BEGIN 
    UPDATE table1
    SET table1.name = replace(table1.name,'x','*')
    FROM table1
    INNER JOIN inserted i on i.name = table1.name
END 

i suggest if you have an ID in the table to use it in the joins to find the exact record inserted to make it accurate like below

CREATE TRIGGER ReplacingTrigger 
ON  table1  
AFTER INSERT 
AS  
BEGIN 
    UPDATE table1
    SET table1.name = replace(table1.name,'x','*')
    FROM table1
    INNER JOIN inserted i on i.id= table1.id
END 
share|improve this answer
    
Sample Name was just a sample, Actually I wanna change all × inserting chars to * –  Mohammad Jun 26 '12 at 5:50
    
just to understand you better, you have a column called name that you want to update, by replacing specific characters every time an insertion happens –  AmmarR Jun 26 '12 at 5:57
    
Yes, I wanna to change some specific chars whenever a string is inserted into the Name field –  Mohammad Jun 26 '12 at 6:14
    
i updated the main post, i added code for trigger –  AmmarR Jun 26 '12 at 7:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.